How long does my code take to run – SQL?

If you are like me, and you probably are not, then you might come across occasions where you would like to know how long your code is taking to run.  For me, it was the desire to compare code I had written against someone else code to see who’s was faster / more efficient, etc. (Bragging rights?)

In .NET I have a class file to handle this sort of thing.  Simply call the class, pass in a start and end time, and the return value is what you desire.  This method takes one preparation line, say to instantiate the class and provide a ‘start time’, then a completion line, placed after the code I want to measure, along with the ‘End Time’.  I have found it simpler to request both start and end times as this just allows you to measure between any two points in time, not just a point in time that passed moments ago and right this very moment. Or this new very moment.  etc.  My class file also handles storage of historical data for comparisons at a later date (and half a dozen other cool things that a statistician like myself might like to know over time).

I decided on writing my own SQL Function (provided below) which would allow two time inputs, manipulate the time inputs and return a table of values, allowing the caller to decide on which format or set of values they wish to display / manipulate etc.

My chosen outputs (as a table) include (but not limited to) a formatted String of HH:MM:SS:ms, and a Decimal Value representing the total Milliseconds between the two dates. As you can see from the below function I have not been interested in Year, Month, Day kind of thing, only the Hours, Minutes, Seconds and Milliseconds.  I did try to include Nanoseconds, however I still utilize a few SQL 2005 Servers and wanted to make a portable function (with few minor tweaking requirements, not included in example).  All of the below code has been tested on SQL 2012 Server.


CREATE FUNCTION [dbo].[utf_ConvertSecondsToLengthOfTime] (
 @StartTime TIME,
 @EndTime TIME
)

RETURNS @T TABLE([Hours] CHAR(2), [MINUTES] CHAR(2), [SECONDS] CHAR(2), [MILLISECONDS] CHAR(3), [HH:MM:SS:ms] CHAR(12) , [TotalMilliSeconds] DECIMAL(12,3)) 

AS

BEGIN

 DECLARE @ms as INT
 SET @ms = DATEDIFF(ms, @StartTime, @EndTime) / 1000 

 --Convert miliseconds into useable numbers
 DECLARE @Hours AS INT
 SET @Hours= @ms/3600;

 DECLARE @Minuets AS INT
 SET @Minuets = CAST(( @ms % 3600)/60 AS INT) ;

 DECLARE @Seconds AS INT
 SET @Seconds = CAST((@ms % 3600) % 60 AS INT) ;

 DECLARE @MilliSeconds AS INT
 SET @MilliSeconds =CAST(RIGHT(DATEDIFF(MILLISECOND, @StartTime, @EndTime) , 3) AS INT);

 DECLARE @Totals as DECIMAL(12,3)
 SET @Totals =(@Hours * 216000) + (@Minuets * 3600) + (@Seconds * 60) + CAST(@MilliSeconds AS DECIMAL(12,3)) / 1000;

 --Convert Numbers into formatted strings.
 -- Doing all of this in a two stage process makes error checking much simpler.

 DECLARE @sHours AS CHAR(2)
 SET @sHours = RIGHT ('00' + LTRIM(STR(@ms/3600)), 2 );

 DECLARE @sMinuets AS CHAR(2)
 SET @sMinuets = RIGHT ('00' + LTRIM(STR(@Minuets)), 2 );

 DECLARE @sSeconds AS CHAR(2)
 SET @sSeconds = RIGHT ('00' + LTRIM(STR(@Seconds)), 2 );

 DECLARE @sMilliSeconds AS CHAR(3)
 SET @sMilliSeconds = RIGHT ('00' + LTRIM(STR(@MilliSeconds)), 3) ;

 INSERT INTO @T ([Hours], [MINUTES], [SECONDS], [MILLISECONDS], [HH:MM:SS:ms] , [TotalMilliSeconds])
    VALUES (@sHours,@sMinuets, @sSeconds , @SMilliSeconds, @sHours + ':' + @sMinuets + ':' + @sSeconds + ':' + @SMilliSeconds, @Totals) ;

 RETURN

END

/*
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] (CAST('10:03:54.9398338' AS TIME) , CAST('10:03:54.9710340'AS TIME) )

SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:54.0000000' ) --Identical
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:54.5555555' ) --Half a Second

SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:55.0000000' ) --One Second
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:55.0000000' , '10:04:55.0000000' ) --One Minute

SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.0000000' , '10:03:55.5000000' ) --One Second
SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:55.0000000' , '10:04:55.5000000' ) --One Minute

SELECT [TotalMilliSeconds] FROM [dbo].[utf_ConvertSecondsToLengthOfTime] ('10:03:54.9398338' , '12:03:55.9710340' )
*/

And now, it is possible to calculate how long it took for a piece of SQL Code to run. Similar to the following: NB: the function name is now obsolete, but i am sure you can modify it more appropriately.


 DECLARE @StartTime Time
 SET @StartTime = CAST(SYSDATETIME() AS TIME) 

 SELECT * FROM wherever

 SELECT [TotalMilliSeconds] FROM [dbo].[utf_ConvertSecondsToLengthOfTime] (@StartTime, SYSDATETIME() )

So, I decided to put my code to the test and see how fast (slow?) it was.  Regardless of efficiency, I believe it would be simpler to call this than to have to re-write it every time I have a question on how long code is taking to run.

DECLARE @TestingTime INT =3
DECLARE @LoopStartTime DATETIME2 = GETDATE()

DECLARE @LoopEndTime DATETIME2
SET @LoopEndTime = DATEADD(s,@TestingTime,@LoopStartTime)

WHILE GETDATE() <= @LoopEndTime -- DateDiff(s, 1, @LoopStartTime)
BEGIN
 PRINT SYSDATETIME() --50,804
END

SET @LoopStartTime = GETDATE()
SET @LoopEndTime = DATEADD(s,@TestingTime,@LoopStartTime)

WHILE GETDATE() <= @LoopEndTime -- DateDiff(s, 1, @LoopStartTime)
BEGIN

 SELECT * FROM [dbo].[utf_ConvertSecondsToLengthOfTime] (@LoopStartTime, @LoopEndTime ) -- 76
END

And the results.

Seconds SYSDATETIME() Function
1 50,804 76
2 121,173 121
3 165,302 136