Solved

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

Posted on 2009-05-07
10
959 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Sum of items in two tables not equal. 5 49
sql query 5 44
SQL Server code help needed 14 34
Need to trim my database size 9 26
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…
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 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