SQL Server Performance Forum – Threads Archive

Resolving function calls

Hi when you have multiple calls to a function in a WHERE Clause does the compiler resolve the call once and pass a token to the other calls or is each call resolved individually. For instance: SELECT * FROM SOMETABLE
WHERE
Date_Hired < GetDate()
AND Date_Promoted < GetDate() Does the compiler call GetDate() once or twice. Also if a full table scan if being performed does the function get called for each row.
i.e. if you have a 10,000 row table and are performing a full scan and have a GetDate() call in you SQL statement is GetDate() called 10,000 times. I guess the answer to the first question coves this as well. The compiler resolves the call once and passes a token in a set based solution it should do the same thing in a full table scan. Thanks
Mike

I think GetDate() will be called twice for every row
Madhivanan Failing to plan is Planning to fail

Actually, from looking at the execution plans, it appears the function is called once per statement. It is then compared against the appropriate scans or seeks, and used to populate the appropriate expressions. Look at the execution plans when you call this code. SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate() SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
BEGIN TRANSACTION testgetdate
print ‘begin second test’
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate() SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
SELECT *, getdate(), getdate() FROM Employees
WHERE
BirthDate < GetDate()
AND HireDate < GetDate()
commit transaction gettestdate
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Thanks, would you look at the execution plans for the following. I tested this both with and without getdate()in the SELECT. (SELECT *,GETDATE(),GETDATE() and SELECT *). I am not sure how to interpertet the results of these test. When GETDATE wsa placed in the Select *,GETDate(),GETDate() an additional Compute Scalar shows up in the execution plan. Without the getdate in the select there is only one compute Scalar in the execution plan. Each compute scalar shows CPU usage of .000001. When Using @VAR both with and without the extra getdate()s there is only one compute scalar in the execution plan which consumes no measurable cpu time. This was using Northwind which is a very small dataset. Would this CPU usage go up for a larger DATASET? AND is this CPU usage enough to justify using variables in place of the function calls to get date. Thanks
Mike

Gee it would help if I posted the code I was asking you to look at. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> BEGIN TRANSACTION test –‘begin second test' DECLARE @Var DATETIME SET @VAR= getdate() SELECT *,GetDate(),GetDate() FROM Employees WHERE BirthDate < GetDate() AND HireDate < GetDate() SELECT *,GETDATE(),GETDATE() FROM Employees WHERE BirthDate < GetDate() and HireDate < GetDate() SELECT *,GETDATE(),GETDATE() FROM Employees WHERE BirthDate < GetDate() and HireDate < GetDate() SELECT *,GETDATE(),GETDATE() FROM Employees WHERE BirthDate < GetDate() and HireDate < GetDate() SELECT *,GETDATE(),GETDATE() FROM Employees WHERE BirthDate < GetDate() and HireDate < GetDate() SELECT *,GETDATE(),GETDATE() FROM Employees WHERE BirthDate < GetDate() and HireDate < GetDate() SELECT *,@VAR,@VAR FROM Employees WHERE BirthDate < @Var AND HireDate < @Var SELECT *,@VAR,@Var FROM Employees WHERE BirthDate < @Var and HireDate < @Var SELECT *,@VAR,@Var FROM Employees WHERE BirthDate < @Var and HireDate < @Var SELECT *,@VAR,@Var FROM Employees WHERE BirthDate < @Var and HireDate < @Var SELECT *,@VAR,@Var FROM Employees WHERE BirthDate < @Var and HireDate < @Var SELECT *,@VAR,@Var FROM Employees WHERE BirthDate < @Var and HireDate < @Var commit transaction test

I wouldn’t worry about using one getdate() with a variable instead of multiple getdate() functions unless you need the getdate() to be consistent throughout a batch. Remember, that it is only the same for one statement. Anyone else have an opinion on this one? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

quote:Would this CPU usage go up for a larger DATASET? AND is this CPU usage enough to justify using variables in place of the function calls to get date.

No and no. I agree with Derrick.

Thanks I did not really think that it would be worthwhile unless the call to getdate was called for every row in the database in which case it could make a measurable difference. But on the other hand it would not have been the first time that I was wrong. Thanks again for your input
Mike

]]>