Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678

Hidden Formatting Troubles with STR() (SQL Spackle) Expand / Collapse
Author
Message
Posted Sunday, January 5, 2014 5:29 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:58 PM
Points: 3,422, Visits: 5,366
Jeff Moden (1/3/2014)

...

Ok... that tears it. 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1527942
Posted Monday, January 6, 2014 1:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
First, thanks for the test. On the subject of defending MS for the slowness of FORMAT based on the fact that it's a generic function, I'm sure that it was designed to operate at the machine language level and the fact that it's 119 times slower than some T-SQL "hack" code is absolutely deplorable. They should turn in their "man" cards as punishment.

On the SET STATISTICS thing, I haven't looked for evidence that it affects anything other than Scalar and Multi-Statement UDFs (not to be confused with iTVFs) but it has made me very, very cautious in its use. It would have been a joy if that was the reason why FORMAT ran so slow instead of it falling into the category of being seriously performance challenged code.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527992
Posted Tuesday, January 7, 2014 4:27 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 1,125, Visits: 1,506
Sorry if I'm just being dense by asking this, I've never used STR() either so this is new to me. The article says many people use STR() for its ability to right-justify figures, and that besides the rounding issues, which I understand and am now aware of, there is a performance penalty when using it. You then run a timed test to compare it to a more complex, but quicker approach. However when I run a modified version of the code (to strip out the timing code - I'll take your word for it it's quicker) and the output to a variable, the faster approach doesn't right justify the data like STR() does.

Here is what I'm running (limited to 10 rows here)

--===== Conditionally drop and rebuild a test table in a nice safe place
-- that everyone has. This only takes several seconds.
IF OBJECT_ID('tempdb..#MyHead','U') IS NOT NULL
DROP TABLE #MyHead
;
SELECT TOP 10
SomeNumber = IDENTITY(INT,1,1)
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
SELECT RIGHT('0000000000' + CAST(SomeNumber AS VARCHAR(10)),10)
FROM #MyHead
;
SELECT STR(SomeNumber,10)
FROM #MyHead

and here is the output

(10 row(s) affected)

----------
0000000001
0000000002
0000000003
0000000004
0000000005
0000000006
0000000007
0000000008
0000000009
0000000010

(10 row(s) affected)


----------
1
2
3
4
5
6
7
8
9
10

(10 row(s) affected)

Is there something I'm not understanding, or am doing incorrectly? I would expect that any alternative would output the same results as STR(). Any insight would be appreciated.

Thanks

Paul
Post #1528417
Posted Tuesday, January 7, 2014 7:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
Sure. There are actually three implied reasons. The first is that zeros are simply easier to see and verify the presence of in an article than a blob of spaces are. The second is to show a bit of flexibility of using different characters, the more common of which is "0". The third is that "a lot of people" have to produce output for fixed length files and the numeric fields need to be left-padded with zeros, something that the already slower STR() doesn't do without extra code. Those weren't specifically mentioned in the article because I didn't want to distract folks from the main purposes of the article. As with any article, there is the potential for casual learning. Hopefully, even beginners will immediately understand that you should simply replace the zeros with spaces to make the output like that of the STR() function.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1528467
Posted Tuesday, January 7, 2014 7:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:26 AM
Points: 1,125, Visits: 1,506
Thanks Jeff, makes sense. If someone asked me to right justify some text I think the quicker solution would be closer to what I would have attempted anyway as i'm more used to using those sorts of string manipulations, but at least now I know for sure it's the better implementation

Thanks

Paul
Post #1528475
Posted Tuesday, January 7, 2014 7:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:53 AM
Points: 35,540, Visits: 32,123
paul.goldstraw (1/7/2014)
Thanks Jeff, makes sense. If someone asked me to right justify some text I think the quicker solution would be closer to what I would have attempted anyway as i'm more used to using those sorts of string manipulations, but at least now I know for sure it's the better implementation

Thanks

Paul


That being said, I'll take that as a suggestion and modify the article to use spaces at first as a direct replacerment for STR() and then expose the ability to use other characters and the reasons why one might want to do so.

Thanks for the feedback, Paul.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1528477
Posted Tuesday, January 7, 2014 4:56 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:58 PM
Points: 3,422, Visits: 5,366
Jeff Moden (1/7/2014)
paul.goldstraw (1/7/2014)
Thanks Jeff, makes sense. If someone asked me to right justify some text I think the quicker solution would be closer to what I would have attempted anyway as i'm more used to using those sorts of string manipulations, but at least now I know for sure it's the better implementation

Thanks

Paul


That being said, I'll take that as a suggestion and modify the article to use spaces at first as a direct replacerment for STR() and then expose the ability to use other characters and the reasons why one might want to do so.

Thanks for the feedback, Paul.


Sounds like that's above and beyond the call of a Spackle article!



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1528715
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse