A combination of string and math based solutions were posted as potential alternatives. A recent post highlighted the use of the msdb function agent_datetime. All of these math functions posted will provide an accurate second value from a SQL Agent duration without regard to sign (positive or negative) or size (greater than 6 digits).
All of the string methods mentioned, including the agent_datetime function, rely on three facts to be true.
1) All values in the table must either be zero or positive integersWhile I understand that the existence of any of these values is a sign of a larger issue – if the code generating the report fails, the report is incapable of telling me there is a problem. I generally run reports on new environments to understand what the current state is and what needs to be worked on. It is not uncommon to find jobs that have run more than a day in a new environment. While less common, I have also had to deal with negative duration values stored in the sysjobhistory. I agree – neither is ideal and neither should exist. However, it is much more common for me to be called to fix what is broken, not to watch an already stable and perfect environment.
2) No value greater than 235959 can exist in the table because of datetime conversion issues.
3) Positions 2-3 and Positions 4-5 must not be greater than 60 due to datetime conversion issues.
Testing PerformanceI have never actually tested the performance of using the math based solution vs. the string manipulation solutions. So, I devised a process to put all of these suggestions to a performance test. I first created a table with integers –1971000 through 1971000. I understand this is extreme, in both directions, but I had to use something large enough to get some idea of performance.
You can examine the T-SQL at the end of this post. At each test, the T-SQL represents a proposed solution on the forum thread. Each test converts each of the integer values in the table to a number of seconds based upon the HHMMSS rule of how these durations are stored in sysjobhistory. These are populated into a variable so that any transfer to the client or IO issues are eliminated in the performance test. The only thing being tested should be CPU and memory throughput. In order to adjust for the performance of any given environment, I establish a control. This control does no conversion on the integer value, simply selects each one into a variable. This should provide a good baseline for what the maximum performance could be.
Given that each of the string functions proposed fail unless provided very specific numeric values, I eliminated any negative values, any values > 235959, and any values where the HH and SS values were > 59 to avoid datetime conversion errors.
Below is the average of each of these methods (and a control) over 10 executions for both my laptop and an enterprise class server.
|235959 Mod Math Implicit||87||38|
|235959 Mod Math||90||44|
|235959 Mod Math Intrope||123||44|
|Full Mod Math||1755||790|
|Full Mod Math Implicit||1814||867|
|Full Mod Math Intrope||2377||1077|
Admittedly, the difference in these test scenarios is negligible. However, in all cases, using the mod operator and the implicit truncation within SQL Server was the fastest conversion method. In addition to being the fastest – it’s the most compact visibly and works for just about any values – not just those less than a single day. I encourage you to run these tests in your environment and see which solution comes out on top.
As with anything in SQL Server, when asked which method is “better” I would have to say “It depends.” However, if you ask which appears to be fastest, which works for most conceivable situations, and which will I use? I’m going to stick with math – and leave the strings be.
About Kyle Neier
Husband of a magnificent woman, father of 5, SQL Server geek, IndyPASS Vice President and Food Guy, DBA automation zealot, amateur Powershell evangelist. Follow Me on Twitter