Solved

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

Posted on 2009-05-07
10
925 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

759 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

21 Experts available now in Live!

Get 1:1 Help Now