Monday, July 07, 2008

A while ago I blogged about how to get the date element of a datetime column in TSQL. In that post I said

I would probably have done it via a CONVERT/CAST operation, converting to a VARCHAR and then back to a DATETIME, but this is a much more efficient method.

but I didn't prove it at the time. I gave it some more thought and wanted to know what the differences were, so I wrote some timings code.

This script creates one table Timings with columns of Code, Description, ActionTime and IsComplete. It has a combined primary key of Code and IsComplete. Code must be unique - and can be a string of up to 10 characters long to uniquely identify the action being timed. IsComplete is used to differentiate between the start time and end time of the process being monitored.

The script also creates 3 stored procedures:

• up_RecordStart which takes 2 parameters - the unique code and optional description. This is used to record the start of the activity being monitored.
• up_RecordEnd which takes just 1 parameter - the code - should match the code used in up_RecordStart. This is used to record the end of the activity being monitored.
• up_GetTimings which again takes just 1 parameter - the code to return the timings from. It then returns the Code, Description and the length of time the action took in ms.

I wrote some script to then use these objects to test the assertion I made that FLOOR and combinations of converting DATETIME to FLOAT etc would be more efficient than using either CAST or CONVERT to VARCHAR(12) and back again to a DATETIME.

------------------------------
-- Clean up before we start --
------------------------------
DELETE FROM Timings
WHERE Code IN ('FLR','CONVERT','CAST')
GO

---------------------
-- Try using Floor --
---------------------
EXEC up_RecordStart @Code='FLR', @Description='SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))'
GO

DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0

WHILE @i < 1000000 -- try the next statement for 1000000 times - this should be enough to see some differences
BEGIN

  SET @floorDate = CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))
  SET @i = @i + 1
END
PRINT @floorDate
GO

EXEC up_RecordEnd @Code='FLR'
GO

-----------------------
-- Try using convert --
-----------------------
EXEC up_RecordStart @Code='CONVERT', @Description='SELECT CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))'
GO

DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000
BEGIN

  SET @floorDate = CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))
  SET @i = @i + 1
END
PRINT @floorDate
GO

EXEC up_RecordEnd @Code='CONVERT'
GO

--------------------
-- Try using Cast --
--------------------
EXEC up_RecordStart @Code='CAST', @Description='SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)'
GO

DECLARE @i AS INTEGER
DECLARE @floorDate AS DATETIME
SET @i = 0
WHILE @i < 1000000
BEGIN

  SET @floorDate = CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)
  SET @i = @i + 1
END
PRINT @floorDate
GO

EXEC up_RecordEnd @Code='CAST'
GO

-------------------------
-- Now get the timings --
-------------------------
EXEC up_GetTimings 'FLR'
GO

EXEC up_GetTimings 'CONVERT'
GO

EXEC up_GetTimings 'CAST'
GO

This results in the following data being returned:

Code	Description	TimeInMS
FLR	SELECT CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())))	1313
CONVERT	SELECT CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE()))	3236
CAST	SELECT CAST(CAST(GETDATE() AS VARCHAR(12)) AS DATETIME)	3203

which shows that the method using FLOOR is more efficient, and that there isn't a lot to chose between CONVERT and CAST

Labels: SQL, SQLServer2005

// posted by Jane @ 7:11 PM   

Comments:

Post a Comment