Kyle Neier , (8/24/2012)
It seems that my article about Powershell has sparked some T-SQL discussion, so I decided to take the prompt and run some performance analysis on the different solutions presented here.You can read the detail over on my blog (http://sqldbamusings.blogspot.com/2012/08/t-sql-performance-of-sql-agent-duration.html), but essentially, the conversions that use math instead of string manipulation seem to be faster and cover a wider range of potential values.
Before I ran the tests, I really had no clue what the results would be, which is why I wanted to run them. Thanks to all of you the great suggestions - another testament to how great the SQL community is!
Well, that is interesting.
Carolyn, I wasn't aware of that msdb sys function, so thanks for pointing it out.
That said, I was pretty surprised to find that my hack string manipulation performed 7X faster than the msdb function agent_datetime. Thanks for testing it!
I suppose if I were running a lot of code against a very large table of job histories, I might go back and tweak my code. I suspect that most DBAs running code against msdb.dbo.sysjobhistory are doing it for similar reasons though: a once-a-day (or thereabouts) system check and reporting. Still, I prefer your approach, both for performance and pedagogically: why deal with times as strings?
Kyle, have you seen any documentation anywhere as to why MS has coded times this way?
Thanks
Rich
Thanks for