Solved

cast(cast(getdate() as int) as smalldatetime)  in WHERE clause.  Executed ONCE or EVERY ROW?

Posted on 2009-05-07
10
934 Views
Last Modified: 2012-05-06
See code below.  Does SQL Server optimize the right-hand-side formula of the expression to only evaluate it once?  Or does it needlessly execute it for every single row?  The RHS is essentially a constant in this case and won't change on a per-row basis.

Sounds like in these situations it's better to store the value in a @variable ahead of time then just reference the @variable in the WHERE clause.
select MyColumn from MyTable where SomeColumn < cast(cast(getdate() as int) as smalldatetime)

Open in new window

0
Comment
Question by:ZuZuPetals
  • 5
  • 4
10 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 75 total points
ID: 24327024
It will execute only once
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 125 total points
ID: 24328173
The query optimiser is smart enough to knwo that it is a scaler 'constant' computation and will hold the value.

But that is not always true. Say it was on a derived column, then it will recompute every time, or, even more burdonsome running that on the datasource column (e.g. on "SomeColumn")

as a general rule of thumb, and if you are running in a multi-step / procedural environment (like a stored procedure or script) then it is a good habit to get into calculating once and use the variable. Another rule of thumb is always look at trying to leave the datasource.column in as native/natural a state as possible and play with the variables. we often see things (with dates especially) like convert(varchar,my_date,112) = convert(varchar,getdate(),112) to get rid of the time component, and really is sometimes better expressed as my_date between @start_of_day and @end_of_day where those two variables are pre-calculated before use.

Does that make sense ?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
ID: 24331965
And finally, this:
cast(cast(getdate() as int) as smalldatetime)

may not do what you expect.  Try executing it after noon and you will find it returns the next day.  You are better off with this function:
DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24336270
and this one is pretty quick as well in large queries : convert(datetime, floor(cast (getdate() as decimal(18,6))))

but if using dates, then often best to use the date functions available as per acperkins post...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24336612
Actually I would be surprised if you found anything faster than:
DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24338502
Well, I did not say it was quicker, just pretty quick, and best to use date functions....

But now that you have said "be surprised", then I wondered just how they did measure up... and found datediff is a bit quicker, but not by much on my pissy laptop...

declare @st datetime
set @st = getdate()
declare @d datetime
declare @i int
set @d = '19000101 21:22:23'
set @i = 0
while @i < 2000000
begin
   set @d = @d + 1
   set @d = convert(datetime, floor(cast (@d as float)))
   set @i = @i + 1
end
select getdate() - @st       -- 11.64 sec


declare @st datetime
set @st = getdate()
declare @d datetime
declare @i int
set @d = '19000101 21:22:23'
set @i = 0
while @i < 2000000
begin
   set @d = @d + 1
   set @d = DATEADD(day, 0, DATEDIFF(day, 0, @d))
   set @i = @i + 1
end
select getdate() - @st       -- 11.29 sec
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24338560
Really Mark you need to get yourself a faster laptop (either that or that 220V does not cut it):
10.93
10.843

:)
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24338648
aawww gosh...  I am running a lot of stuff on it :)

And it is a pentium 3 with 256k RAM - what more do you need ?

Just kidding, but it is three years old and been thinking about upgrading, if only I could justify the cost... bit of a bummer being the boss and controlling the purse strings, not to mention being largely broke...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24338769
Actually, this biggest difference is if you comment out this line:
set @d = @d + 1
8.347
8.457

If you try it like this (I run it twice every time):

Declare @From int,
      @To int,
      @Dat smalldatetime,
      @Start datetime

Select      @From = 1,
      @To = 10000000,
      @Start = GETDATE()

While @From <= @To
    Begin
      Select      -- @Dat = CAST(DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) as smalldatetime),
            -- @Dat = CONVERT(char(10), GETDATE(), 101),
            @Dat = CONVERT(datetime, FLOOR(CAST(GETDATE() as float))),
            @From = @From + 1
    End

Print DATEDIFF(millisecond, @Start, GETDATE())

Select      @From = 1,
      @Start = GETDATE()

While @From <= @To
    Begin
      Select      --@Dat = CAST(DATEADD(day, 0, DATEDIFF(day, 0, GETDATE())) as smalldatetime),
            -- @Dat = CONVERT(char(10), GETDATE(), 101),
            @Dat = CONVERT(datetime, FLOOR(CAST(GETDATE() as float))),
            @From = @From + 1
    End

Print DATEDIFF(millisecond, @Start, GETDATE())

I get (on my workstation):
28520, 28373
And:
27703, 30656        -- Not sure why the discrepancy here.

So you are right, there is not a lot of difference.

What you do not want to do if you are purist about speed is the following (favored by some on this site)

CONVERT(datetime, CONVERT(char(8), GETDATE(), 112), 112)

This produces (again, on my workstation):
35343, 31423
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24338925
No need for me to try it - all it's going to prove is yours is bigger than mine - laptop that is ;)

Yeah, have seen that other convert a bit too often, considering : convert(char(8),getdate(),112) will dynamically/implicitly convert to datetime anyway (when used in date context). But have to admit that I am occassionally guilty of similar (less obvious) things...

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now