Will stored procedures "hard-code" functions that return a constant? 
Author Message
 Will stored procedures "hard-code" functions that return a constant?

(SQL Server 2000, SP3)

Hello, all!

I'm wondering whether SQL Server will replace the value of a deterministic function with
the actual value that would be returned, in an effort to avoid calling the function during
the SP execution?

For example, if I had:







Would that be better to represent as:







Or would it be equivalent, performance-wise?

Thanks for any help you can provide!

John Peterson



Mon, 15 Aug 2005 23:03:58 GMT
 Will stored procedures "hard-code" functions that return a constant?

John,

what you are refering to is also called constant folding: the compiler
evaluates the function once and during execution the precalculated constants
are used instead of evaluating the expressions for every execution. SQL
Server 2000 does NOT do constant folding. However, we are looking into
providing this performance improvement in future versions of SQL Server.

- Peter Zabback

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.


Quote:
> (SQL Server 2000, SP3)

> Hello, all!

> I'm wondering whether SQL Server will replace the value of a deterministic
function with
> the actual value that would be returned, in an effort to avoid calling the
function during
> the SP execution?

> For example, if I had:







> Would that be better to represent as:







> Or would it be equivalent, performance-wise?

> Thanks for any help you can provide!

> John Peterson



Tue, 16 Aug 2005 19:24:15 GMT
 Will stored procedures "hard-code" functions that return a constant?
Thanks, Peter!  I'll use the constants, then, instead of the POWER() function.  :-)



Quote:
> John,

> what you are refering to is also called constant folding: the compiler
> evaluates the function once and during execution the precalculated constants
> are used instead of evaluating the expressions for every execution. SQL
> Server 2000 does NOT do constant folding. However, we are looking into
> providing this performance improvement in future versions of SQL Server.

> - Peter Zabback

> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.



> > (SQL Server 2000, SP3)

> > Hello, all!

> > I'm wondering whether SQL Server will replace the value of a deterministic
> function with
> > the actual value that would be returned, in an effort to avoid calling the
> function during
> > the SP execution?

> > For example, if I had:







> > Would that be better to represent as:







> > Or would it be equivalent, performance-wise?

> > Thanks for any help you can provide!

> > John Peterson



Tue, 16 Aug 2005 19:57:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. "return" from stored procedures to ASp

2. "Code" = "code" in Store Procedure

3. Help me about "return code 88"

4. function returning "table"

5. Help "Return from Procedure"

6. max of ("...","...","..")

7. "Global Constants" in SQL Server

8. "Constants" in T-SQL

9. The Constant "DataSearch"

10. Stored Procedure - Varying "Where" Criteria

11. Global Variables from stored procedure "vanishing"

12. Stored Procedure with Optional "Where" Parameters


 
Powered by phpBB® Forum Software