April 25, 2012 at 5:50 pm
Hi all.
I had in the back of my mind for some reason that SPACE() was faster in a SELECT query than REPLICATE() when padding with spaces, but I don't remember why I think that, and I can't find any documentation either way.
Does anyone have any anecdotal evidence to prove or disprove my claim? If I'm wrong, I'll gladly admit it and change my code, but I want to make sure it's worth my time to do so.
Thanks in advance!
Jeff
April 25, 2012 at 6:47 pm
Jeff,
There's nothing like proving it to yourself rather than relying on anecdotal evidence.
Try creating a test harness where you INSERT 1000000 rows of data, first with SPACE and then with REPLICATE. This way you can closely simulate the actual data you're creating (e.g., length of the string). Jeff Moden's article will tell you how to create the harness: http://www.sqlservercentral.com/articles/Data+Generation/87901/. For this case, you can ignore the random numbers as they're irrelevant to you.
Include SET STATISTICS IO ON before the INSERTs and analyze the CPU and actual execution time statistics. Run it several times and look at averages.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply