April 8, 2003 at 4:52 pm
I saw the script contributed by ispaleny and I want to comment on it.
DECLARE @DateTime DATETIME
SET @DateTime = '31 Mar 2003 23:59:59.997'
-- Method 1
SELECT CONVERT(DATETIME, DATEDIFF(DAY, 0, @DateTime))
-- Method 2
SELECT CONVERT(DATETIME, CONVERT(CHAR(8), @DateTime, 112), 112)
-- Method 3
SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, @DateTime)))
Which is faster? How can this be tested/proven?
Is CONVERT(FLOAT, @DateTime) deterministic?
April 9, 2003 at 4:11 am
Method 2 would have made better sense to just do
SELECT CONVERT(DATETIME, CONVERT(CHAR, @DateTime, 112))
However, I had not seen method 1 before and I would say I like it very much now. Overall I think Method 1 thou is the fastest (you woul have to really come up with an odd way to test thou).
Here is my reasoning.
Method 2 -- Even though it won't show in the Execution plan "CONVERT(CHAR(8), @DateTime, 112)" will actually perform 1 read against the master database syslanguages (I believe that is right one) table to get the dateformat. You can however find out for sure by watching what happens in Profiler. So you are bound to the performance condition of the server when reading data from the database so hard drive IO comes into play.
Method 3 takes the converts to the float numeric value of the date time value. Then it removes all decimal vals and converts back to datetime. You actually have to perform 3 functions to get your value, this means a bit extra overhead in memory as oppossed to method 1.
Method 1 of course performs the change in 2 functions and does not have to make any reads against any database besides the one involved in the query. I have to give props to method 1 as it solves many of the concerns I have had to get the day without the time.
As for deterministic.
From BOL
quote:
Deterministic unless used with datetime, smalldatetime, or sql_variant. The datetime and smalldatetime data types are deterministic if the style parameter is also specified.
So since you are converting to float the answer should be it is non-deterministic. Which means potentially there could be a condition where the output is different, but floor should handle as it is not worried with rounding values just the whole number protion.
April 9, 2003 at 6:32 am
Thinking further about this it might be even better to do this
SELECT DATEADD(d,DATEDIFF(d, 0, @DateTime),0)
Which means a common method for first day of month and year.
-- Method Month
SELECT DATEADD(m,DATEDIFF(m, 0, @DateTime),0)
-- Method Year
SELECT DATEADD(yyyy,DATEDIFF(yyyy, 0, @DateTime),0)
April 9, 2003 at 7:28 am
quote:
...you woul have to really come up with an odd way to test thou)....
Well, I know this is crude testing but I ran the following:
/* Make sure in cache so don't skew test */
SET NOCOUNT ON
SELECT TOP 5000 ReceiveDate FROM Order
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT TOP 5000 CONVERT(DATETIME, DATEDIFF(DAY, 0, ReceiveDate )) FROM Order
SELECT TOP 5000 CONVERT(DATETIME, CONVERT(CHAR(8), ReceiveDate , 112)) FROM Order
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
and I got the following results:
Table 'Order'. Scan count 1, logical reads 221, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 50 ms, elapsed time = 204 ms.
Table 'Order'. Scan count 1, logical reads 221, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 437 ms.
To be sure, I reversed the calling order and received the following results:
Table 'Order'. Scan count 1, logical reads 221, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 150 ms, elapsed time = 766 ms.
Table 'Order'. Scan count 1, logical reads 221, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 50 ms, elapsed time = 405 ms.
From a preliminary perspective, it looks like Antares was right on the money...CPU and Total processing time is less than half for Method 1 than it is for Method 2.
This might be an interesting article, Antares... 🙂
April 11, 2003 at 1:07 pm
This also seems to work:
SET @DateTime=DATEDIFF(DAY, 0, @DateTime)
This eliminates the CONVERT function.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply