Jeff Moden (1/3/2014)
...Ok... that tears it. :blush: I have to get off my dead hinny and install 2K12. Thanks to folks like you, Wayne Sheffield, and a couple of others, I not falling behind in knowledge of the version but I really need to start playing with it because of things like the test you did above.
I knew some good would come of that!
Jeff Moden (1/3/2014)
I do have a suggestion though. As I pointed out in the article at http://www.sqlservercentral.com/articles/T-SQL/91724/, SET STATISTICS can have some really undesirable impact on what gets reported for performance. Would you try the same test using SQL Profiler and see if you get similar results?
Thanks for that and thanks for the feedback.
Indeed, I am happy to oblige. I didn't expect much difference given the magnitude of the original results I obtained.
SQL Profiler results (1 run):
CPU Reads Writes Duration
Cast, Concatenate, and Size 343 1612 0 369
The STR() Method is SLOWER 858 1612 0 888
The FORMAT() Method is WAY, WAY SLOWER 35319 1612 0 44126
To be fair to Microsoft and FORMAT, the function does a lot more than just what is being done here. It does follow the pattern that I espouse, namely that a generalized tool will usually perform more slowly than one that is specifically tailored to the task at hand. That doesn't mean you shouldn't use them, just that you need to make sure in the end after your query is working that you're satisfied with the performance and that maybe it would take great pains to make it only a little faster (clearly that is not the case here).
I am curious about one thing. I read the article you linked to when it came out and it seemed like the specific case of biased time results for SET STATISTICS was limited to testing of UDFs. At least that is what I (for some reason) assumed when I read it.
I believe you've suggested before that it may not only apply to that limiting case. Do you have some specific evidence of that Jeff? It is not like you to make generalizations unless you've got some evidence to back it up.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St