April 7, 2009 at 9:50 am
Wondering which of these methods is more effiecient for the server. I often use the second to get only the date on a system timestamp to help aggregate on the date. Also, how would you test to see which is more efficient?
April 7, 2009 at 9:53 am
The speed tests I've done have found that "dateadd(day, datediff(day, 0, @Date), 0)" has been the fastest.
The way to test it is to create a table with a million or so rows of dates with times on them, and run each on all of those. Use "set statistics time on", and you'll get a very precise idea of speed. But you have to test it on a lot of rows at once to really get the results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 10:04 am
I just ran tests on this:
set nocount on;
if object_id(N'tempdb..#T') is not null
drop table #T;
create table #T (
ID int identity primary key,
Date datetime,
CleanedDate datetime);
insert into #T (Date)
select top 1000000 dateadd(second, checksum(newid()), 0)
from sys.all_objects t1
cross join sys.all_objects t2;
set statistics time on;
Each took about 2 seconds for 1-million rows, on my machine.
Dateadd version took 1985 milliseconds
Floor version took 2000 milliseconds
String version took 2954 milliseconds
All are CPU time, not total time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 10:19 am
Thanks GSquared. The temp table is useful, and the results are interesting. When I ran I found:
- dateadd function 1266ms
- floor 781ms
- string 1687ms
All times are CPU.
String in both of our tests seems to be the looser. The dateadd and floor methods seem fairly somewhat consistent.
April 7, 2009 at 10:37 am
Hi
Sorry for barging in... The huge advantage of the DATEADD method is that it can also be used to get the first day of year, first day of month, ... So I would suggest to use this if the performance is enough 😉
Greets
Flo
April 7, 2009 at 11:00 am
Flo: Yep. That's why I use that one. Easy to modify for other uses besides just removing time.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 11:35 am
http://sqlinthewild.co.za/index.php/2008/09/04/comparing-date-truncations/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 11:50 am
I have always found the DATEADD/DATEDIFF method to be the fastest when I tested it.
I would avoid using FLOOR method for the simple reason that it is an unsupported method that depends on knowledge of the internal format of the DATETIME datatype, and it is possible that this could be changed in a future version of SQL Server.
The DATEADD/DATEDIFF method depends on the documented behaviour of these functions without worrying about the internal format of DATETIME, so it is less likely that it could be changed in a future version in a way that would break it.
April 7, 2009 at 11:58 am
Michael Valentine Jones (4/7/2009)
I would avoid using FLOOR method for the simple reason that it is an unsupported method that depends on knowledge of the internal format of the DATETIME datatype, and it is possible that this could be changed in a future version of SQL Server.
It doesn't work on DATETIME2. The dateadd/datediff does.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2009 at 12:07 pm
Okay - even though there seems to be enough correct answers, I wanted to post my results as well, because I took the time to do this 😀
I used 5 million rows because I am on vacation and had the time :hehe:
--=> Run the 5 million rows - laptop, local instance.
--=> CAST(CONVERT(CHAR(10), date, 101) AS DATETIME)
SQL Server Execution Times:
CPU time = 5897 ms, elapsed time = 45976 ms.
--=> CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)
SQL Server Execution Times:
CPU time = 2823 ms, elapsed time = 46164 ms.
--=> DATEADD(day, DATEDIFF(day, 0, Date), 0)
SQL Server Execution Times:
CPU time = 2138 ms, elapsed time = 45404 ms.
--=> Run the 5 million rows - Older Server, local instance.
--=> CAST(CONVERT(CHAR(10), date, 101) AS DATETIME)
SQL Server Execution Times:
CPU time = 8515 ms, elapsed time = 101099 ms.
--=> CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME)
SQL Server Execution Times:
CPU time = 3625 ms, elapsed time = 106452 ms.
--=> DATEADD(day, DATEDIFF(day, 0, Date), 0)
SQL Server Execution Times:
CPU time = 2782 ms, elapsed time = 104368 ms.
Interesting - it looks as if my local laptop beats the tar off my "dev" server.
-- Cory
April 7, 2009 at 12:21 pm
I agree with Michael that the DateDiff/DateAdd method is probably the best for guaranteed future compatability.
Here's some code that I wrote a very long time ago (long before I sidovered the joy of SET Statistics and Profiler traces) to test various date trunction methods... surprise!
[font="Courier New"]drop table dbo.BigTest
--===== Create and populate a million row test table to test all sorts of data with.
-- (This is my standard test table and it can be added to if necessary)
SELECT TOP 1000000
IDENTITY(INT,1,1) AS RowNum,
CAST(RAND(CAST(NEWID() AS VARBINARY))*50000+1 AS INT) AS UserID,
CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
+ CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65)) AS SomeValue,
'A column for kicks' AS Kicks,
'Still another column just for proofing' AS StillAnother,
CAST(RAND(CAST(NEWID() AS VARBINARY))*100 AS MONEY) AS SomeNumber,
CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME) AS ADate --(>=01/01/2000 <01/01/2010)
INTO dbo.BigTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
GO
--===== Add a calculated column to strip the time from the date.
ALTER TABLE dbo.BigTest
ADD ADateNoTime AS DATEADD(dd,DATEDIFF(dd,0,ADate),0)
--===== A table is not properly formed unless a Primary Key has been assigned
-- (Other indexes may be added for testing purposes)
ALTER TABLE dbo.BigTest
ADD PRIMARY KEY NONCLUSTERED (RowNum)
--===== Add an index to the ADate column for testing index usage
drop index BigTest.IX_BigTest_ADate
CREATE NONCLUSTERED INDEX IX_BigTest_ADate
ON dbo.BigTest (ADate)
GO
CREATE FUNCTION dbo.fnADateNoTime (@ADate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(dd,DATEDIFF(dd,0,@ADate),0)
END
GO
---------------------------------------------------------------------------------------------------
--===== Declare a handfull of variables to measure duration
DECLARE @ServerDurationStart DATETIME
DECLARE @ServerDurationEnd DATETIME
DECLARE @CpuUsageMSStart INT
DECLARE @CpuUsageMSEnd INT
DECLARE @DiskReadWriteStart INT
DECLARE @DiskReadWriteEnd INT
DECLARE @RowCount INT
DECLARE @TargetVariable DATETIME
--=================================================================================================
-- Test the CONVERT/VARCHAR method of stripping time from a date
--=================================================================================================
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--===== Capture the condition of the resources used so far
-- as a starting point.
SELECT @ServerDurationStart = GETDATE(),
@CpuUsageMSStart = SUM(CPU),
@DiskReadWriteStart = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the code being tested
SELECT @TargetVariable = CONVERT(DATETIME,CONVERT(VARCHAR,ADate,101))
FROM dbo.BigTest
--===== Capture the end condition of the resources used so far
-- as an ending point.
SELECT @ServerDurationEnd = GETDATE(),
@CpuUsageMSEnd = SUM(CPU),
@DiskReadWriteEnd = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
-- Capture the rowcount
SELECT @RowCount = @@ROWCOUNT
--===== Print the performance report
PRINT 'Results from the CONVERT/VARCHAR method...'
PRINT STR(DATEDIFF(ms,@ServerDurationStart,@ServerDurationEnd)) + ' :Total server duration (ms)'
PRINT STR(@CpuUsageMSEnd-@CpuUsageMSStart) + ' :CPU Usage (ms)'
PRINT STR(@DiskReadWriteEnd-@DiskReadWriteStart) + ' :Total Disk Reads/Writes'
PRINT REPLICATE('=',78)
--=================================================================================================
-- Test the CAST/FLOOR method of stripping time from a date
--=================================================================================================
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--===== Capture the condition of the resources used so far
-- as a starting point.
SELECT @ServerDurationStart = GETDATE(),
@CpuUsageMSStart = SUM(CPU),
@DiskReadWriteStart = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the code being tested
SELECT @TargetVariable = CAST(FLOOR(CAST(ADate AS FLOAT)) AS DATETIME)
FROM dbo.BigTest
--===== Capture the end condition of the resources used so far
-- as an ending point.
SELECT @ServerDurationEnd = GETDATE(),
@CpuUsageMSEnd = SUM(CPU),
@DiskReadWriteEnd = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
-- Capture the rowcount
SELECT @RowCount = @@ROWCOUNT
--===== Print the performance report
PRINT 'Results from the CAST/FLOOR method...'
PRINT STR(DATEDIFF(ms,@ServerDurationStart,@ServerDurationEnd)) + ' :Total server duration (ms)'
PRINT STR(@CpuUsageMSEnd-@CpuUsageMSStart) + ' :CPU Usage (ms)'
PRINT STR(@DiskReadWriteEnd-@DiskReadWriteStart) + ' :Total Disk Reads/Writes'
PRINT REPLICATE('=',78)
--=================================================================================================
-- Test the DATEADD/DATEDIFF method of stripping time from a date
--=================================================================================================
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--===== Capture the condition of the resources used so far
-- as a starting point.
SELECT @ServerDurationStart = GETDATE(),
@CpuUsageMSStart = SUM(CPU),
@DiskReadWriteStart = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the code being tested
SELECT @TargetVariable = DATEADD(dd,DATEDIFF(dd,0,ADate),0)
FROM dbo.BigTest
--===== Capture the end condition of the resources used so far
-- as an ending point.
SELECT @ServerDurationEnd = GETDATE(),
@CpuUsageMSEnd = SUM(CPU),
@DiskReadWriteEnd = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
-- Capture the rowcount
SELECT @RowCount = @@ROWCOUNT
--===== Print the performance report
PRINT 'Results from the DATEADD/DATEDIFF method...'
PRINT STR(DATEDIFF(ms,@ServerDurationStart,@ServerDurationEnd)) + ' :Total server duration (ms)'
PRINT STR(@CpuUsageMSEnd-@CpuUsageMSStart) + ' :CPU Usage (ms)'
PRINT STR(@DiskReadWriteEnd-@DiskReadWriteStart) + ' :Total Disk Reads/Writes'
PRINT REPLICATE('=',78)
--=================================================================================================
--=================================================================================================
-- Test the CONVERT/INT method of stripping time from a date
--=================================================================================================
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--===== Capture the condition of the resources used so far
-- as a starting point.
SELECT @ServerDurationStart = GETDATE(),
@CpuUsageMSStart = SUM(CPU),
@DiskReadWriteStart = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the code being tested
SELECT @TargetVariable = CONVERT(DATETIME,CONVERT(INT,ADate-.5))
FROM dbo.BigTest
--===== Capture the end condition of the resources used so far
-- as an ending point.
SELECT @ServerDurationEnd = GETDATE(),
@CpuUsageMSEnd = SUM(CPU),
@DiskReadWriteEnd = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
-- Capture the rowcount
SELECT @RowCount = @@ROWCOUNT
--===== Print the performance report
PRINT 'Results from the CONVERT/INT method...'
PRINT STR(DATEDIFF(ms,@ServerDurationStart,@ServerDurationEnd)) + ' :Total server duration (ms)'
PRINT STR(@CpuUsageMSEnd-@CpuUsageMSStart) + ' :CPU Usage (ms)'
PRINT STR(@DiskReadWriteEnd-@DiskReadWriteStart) + ' :Total Disk Reads/Writes'
PRINT REPLICATE('=',78)
--=================================================================================================
--=================================================================================================
-- Test the CALCULATED COLUMN method of stripping time from a date
--=================================================================================================
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--===== Capture the condition of the resources used so far
-- as a starting point.
SELECT @ServerDurationStart = GETDATE(),
@CpuUsageMSStart = SUM(CPU),
@DiskReadWriteStart = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the code being tested
SELECT @TargetVariable = ADateNoTime
FROM dbo.BigTest
--===== Capture the end condition of the resources used so far
-- as an ending point.
SELECT @ServerDurationEnd = GETDATE(),
@CpuUsageMSEnd = SUM(CPU),
@DiskReadWriteEnd = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
-- Capture the rowcount
SELECT @RowCount = @@ROWCOUNT
--===== Print the performance report
PRINT 'Results from the CALCULATED COLUMN method...'
PRINT STR(DATEDIFF(ms,@ServerDurationStart,@ServerDurationEnd)) + ' :Total server duration (ms)'
PRINT STR(@CpuUsageMSEnd-@CpuUsageMSStart) + ' :CPU Usage (ms)'
PRINT STR(@DiskReadWriteEnd-@DiskReadWriteStart) + ' :Total Disk Reads/Writes'
PRINT REPLICATE('=',78)
--=================================================================================================
--=================================================================================================
-- Test the FUNCTION method of stripping time from a date
--=================================================================================================
--===== Ensure that the data is NOT cached so we can get a
-- true measure of performance of the code being tested.
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--===== Capture the condition of the resources used so far
-- as a starting point.
SELECT @ServerDurationStart = GETDATE(),
@CpuUsageMSStart = SUM(CPU),
@DiskReadWriteStart = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
--===== Run the code being tested
SELECT @TargetVariable = dbo.fnADateNoTime(ADate)
FROM dbo.BigTest
--===== Capture the end condition of the resources used so far
-- as an ending point.
SELECT @ServerDurationEnd = GETDATE(),
@CpuUsageMSEnd = SUM(CPU),
@DiskReadWriteEnd = SUM(Physical_IO)
FROM Master.dbo.SysProcesses
WHERE SPID = @@SPID
-- Capture the rowcount
SELECT @RowCount = @@ROWCOUNT
--===== Print the performance report
PRINT 'Results from the FUNCTION method...'
PRINT STR(DATEDIFF(ms,@ServerDurationStart,@ServerDurationEnd)) + ' :Total server duration (ms)'
PRINT STR(@CpuUsageMSEnd-@CpuUsageMSStart) + ' :CPU Usage (ms)'
PRINT STR(@DiskReadWriteEnd-@DiskReadWriteStart) + ' :Total Disk Reads/Writes'
PRINT REPLICATE('=',78)
--=================================================================================================
[/font]
And, here's the results from my box...
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Results from the CONVERT/VARCHAR method...
2780 :Total server duration (ms)
2485 :CPU Usage (ms)
138 :Total Disk Reads/Writes
==============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Results from the CAST/FLOOR method...
796 :Total server duration (ms)
625 :CPU Usage (ms)
138 :Total Disk Reads/Writes
==============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Results from the DATEADD/DATEDIFF method...
576 :Total server duration (ms)
406 :CPU Usage (ms)
138 :Total Disk Reads/Writes
==============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[font="Arial Black"]Results from the CONVERT/INT method...
423 :Total server duration (ms)
266 :CPU Usage (ms)
138 :Total Disk Reads/Writes[/font]
==============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Results from the CALCULATED COLUMN method...
1330 :Total server duration (ms)
438 :CPU Usage (ms)
398 :Total Disk Reads/Writes
==============================================================================
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Results from the FUNCTION method...
3016 :Total server duration (ms)
2906 :CPU Usage (ms)
141 :Total Disk Reads/Writes
==============================================================================
--==========================================================================================================================
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2009 at 12:35 pm
Jeff, the problem with the convert(int) method is that it, again, relies on the internal storage of datetime, just like the version for float.
Have you tested it in SQL 2008 on Datetime2?
Also, unlike the dateadd(datediff) method, it isn't easily extensible to work with other factors than just getting rid of the time. Means less consistency in the code.
For example, just by changing from Day to Hour, I can get the top of the last hour. Change to month, I get the first of the month. Various ranges are easy to generate too, like from beginning to end of the week, month, or day. Not as easy to do with the integer version.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 1:16 pm
GSquared (4/7/2009)
Jeff, the problem with the convert(int) method is that it, again, relies on the internal storage of datetime, just like the version for float.Have you tested it in SQL 2008 on Datetime2?
Also, unlike the dateadd(datediff) method, it isn't easily extensible to work with other factors than just getting rid of the time. Means less consistency in the code.
For example, just by changing from Day to Hour, I can get the top of the last hour. Change to month, I get the first of the month. Various ranges are easy to generate too, like from beginning to end of the week, month, or day. Not as easy to do with the integer version.
Heh... "Must look eye!" ... ya gotta read the disclaimer I put at the top of my post. And, not everything has to be compatible with DateTime2 to be used.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 7, 2009 at 1:25 pm
Jeff Moden (4/7/2009)
GSquared (4/7/2009)
Jeff, the problem with the convert(int) method is that it, again, relies on the internal storage of datetime, just like the version for float.Have you tested it in SQL 2008 on Datetime2?
Also, unlike the dateadd(datediff) method, it isn't easily extensible to work with other factors than just getting rid of the time. Means less consistency in the code.
For example, just by changing from Day to Hour, I can get the top of the last hour. Change to month, I get the first of the month. Various ranges are easy to generate too, like from beginning to end of the week, month, or day. Not as easy to do with the integer version.
Heh... "Must look eye!" ... ya gotta read the disclaimer I put at the top of my post. And, not everything has to be compatible with DateTime2 to be used.
Yeah, I saw the disclaimer after I wrote my post. (I got about 3 hours of sleep last night, so I'm missing a few things that I usually wouldn't.)
And no, not everything has to be compatible with anything except exactly what it's being used for. As mentioned, the main reason I use the add/diff method is that it's easily extensible. It performs well enough that a million row test comes back with a total performance difference of 150 milliseconds. That's a little more than a tenth of a millisecond per use, which I'm willing to sacrifice for consistency and flexibility.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply