Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-07
10
Medium Priority
?
969 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 300 total points
ID: 24327024
It will execute only once
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 500 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 1200 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

661 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