Solved

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

Posted on 2009-05-07
10
965 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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
 
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

688 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