February 27, 2004 at 6:50 am
Good point. As it happens, BETWEEN was first so, if anything, would have been disadvantaged. But, I admit, this wasn't a controlled test and could be affected by many factors. Further, this was on an unindexed column but, in production, you would have an index for a query like this.
February 27, 2004 at 7:12 am
Oh should also backtrack here and say the between should be for that case because you only have to evaluate the date range start and end one time each so the run is straight forward. Whereas the DATEADD(....) method has to evaluate each record on the fly (little lag there), however going back to the original question it was about taking the time off, you cannot at this point but you can set to midnight which evaluates like a date without time. But there can b conditions where DATEADD with a between ircumstance could be the right thing.
Ex.
Business hours I need to see if a place was open on a certain day.
WHERE ('20040202' BETWEEN Str_Open AND Str_Close)
May miss because Str_Open includes time some items.
So change to
WHERE ('20040202' BETWEEN DATEADD(d,DATEDIFF(d,0,Str_Open),0) AND Str_Close)
Now is the Str_Open was say 2/2/2004 8:00AM it will evaluate true.
All circumstances may have a different way to deal with but the key when searching data is how to get it the quickest.
February 27, 2004 at 10:39 am
Try this - I use this to trim the time out of series of datetime fields to group by date only:
cast(substring(cast(Loaddate as binary(8)),1,4) + 0x00000000 as datetime)
set nocount on
set statistics time on
dbcc dropcleanbuffers
select top 100 AccountID, dateadd(d,datediff(d,0,loaddate),0) from Accounts (nolock)
dbcc dropcleanbuffers
SELECT top 100 AccountID, CAST(FLOOR(CAST(loaddate as float)) AS smalldatetime) from Accounts (nolock)
dbcc dropcleanbuffers
select top 100 AccountID, convert(varchar(10),loaddate,120) from Accounts (nolock)
dbcc dropcleanbuffers
select top 100 AccountID, cast(substring(cast(loaddate as binary(8)),1,4) + 0x00000000 as datetime) from Accounts (nolock)
set statistics time off
set nocount off
Results ( Timings for DBCC comands removed ):
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 164 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 61 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 14 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 12 ms.
Profiler shows 0 CPU and 59 Reads for each statement - also confirms the timings.
Nate
February 27, 2004 at 3:02 pm
Great job but I do suggest you try it after you change to the following to clean out the caches.
dbcc dropcleanbuffers
DBCC FREEPROCCACHE
I tested on a system with 1 Million rows and all were mariginally different.
I ran 9 times and each time the on msotly showed 29ms. However I did see some boosts as high as 60ms but at that moment the page file was swapping.
All are valid options, all seem to perform at least for output, now for data comparison you have to understand your needs and and circumstances of different datatypes as far as implicit conversions will do.
For instance say you do
ValueX between convert(varchar(10),startdate,101) and convert(varchar(10),enddate,101)
ValueX being a datetime data type. What happens is in SQL 2000 ValueX will implicitly be convert to varchar type 101 (Which you used 120, this is for example).
What that means is date format mm/dd/yyyy but character order will cause incorrect results.
For Example: ValueX between '1/1/2004' and '2/1/2004'
you would get dates 1/1/2000, 1/1/2003, 1/8/2001 and such becuase of character sort order.
Thus stick with the ISO format. But why have the datatype of the comparign column change if not needed. So the others have a better advatange overall. But I find the DATEADD/DATEDIFF method a bit easier to follow and flexible sine you can use m for month, yyyy for year and such and know what to expect.
Again it is all a matter of preference and performance (which here is really the same in the tests I ran on 3 systems (2 2K, 1 7) ) .
I say all are valid but I prefer DATEADD/DATEDIFF and I have not found a difference with the others to sway me. Before I did use the CONVERT method but changed for preference reasons.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply