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 ««12345»»»

Performance Tuning: Concatenation Functions and Some Tuning Myths Expand / Collapse
Author
Message
Posted Tuesday, January 01, 2008 1:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Mike C (1/1/2008)
Oops you're right, I did miss that code block. I think the speedup is not really due to UDF overhead though, but rather because of FOR XML and xml data type optimizations. In the example I posted it still uses a UDF and it's still really fast.

You can also do something like this with the data() node test, replacing the SUBSTRING and the inline string concatenation with a single REPLACE function call:

SELECT t1.SomeID, REPLACE ((SELECT t2.SomeCode AS 'data()'
FROM dbo.TestData t2
WHERE t1.SomeID = t2.SomeID
FOR XML PATH('')), ' ', ',')
FROM dbo.TestData t1
GROUP BY t1.SomeID;

Glad to bring you over to the XML dark side, even if you're just dipping your little toe in :)

Thanks again Jeff :)


Thanks for the great feedback and the code examples, Mike. Heh... "dark side" indeed... I hate XML :P Still, it seems that's the way a lot of folks have gone and I'm going to do some more heavy testing on some XML things... you know what they say... "Know thy enemy"

Part of the testing isn't to avoid XML... I can see grand use for some of the "new" XML functions like the concatenation example, for example (even though one should severely limit the use of concatenation in SQL).


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #437767
Posted Tuesday, January 01, 2008 1:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
Jeff Moden (1/1/2008)
what a "Triangular Join" actually is? They just don't teach the good stuff in the schools that are supposed to...


I haven't seen a course that even mentioned that anything other than an equi-join is possible. I showed a join on > recently to a developer I work with and his reaction was "You can do that????"

And then there's the problem that a lot of people don't want to learn. That's a whole nother discussion.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #437775
Posted Tuesday, January 01, 2008 2:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Jeff Moden (1/1/2008)

Thanks for the great feedback and the code examples, Mike. Heh... "dark side" indeed... I hate XML :P Still, it seems that's the way a lot of folks have gone and I'm going to do some more heavy testing on some XML things... you know what they say... "Know thy enemy"

Part of the testing isn't to avoid XML... I can see grand use for some of the "new" XML functions like the concatenation example, for example (even though one should severely limit the use of concatenation in SQL).


Hey Jeff, send me your address and I'll fire off a copy of my SQL Server XML book to you in a few months when it goes to press :) You might find some other useful features in there as well :)
Post #437776
Posted Tuesday, January 01, 2008 3:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:12 PM
Points: 7,084, Visits: 14,685
GilaMonster (1/1/2008)
Jeff Moden (1/1/2008)
what a "Triangular Join" actually is? They just don't teach the good stuff in the schools that are supposed to...


I haven't seen a course that even mentioned that anything other than an equi-join is possible. I showed a join on > recently to a developer I work with and his reaction was "You can do that????"

And then there's the problem that a lot of people don't want to learn. That's a whole nother discussion.


You must have slept through that part of the 431 prep...:)

I've been having some extra time on my hands at home (funny - sometimes you get EXACTLY what you wish for for christmas...), so I've taken some time to get back into some of my resolutions and see if I can finish some of these certs I've been dancing around with for a while, now that I am back into consulting.

Not real in depth, but since the collection is targeted at being a primer, I wouldn't expect it to. But still - Inner, left/right/full outer, cross join, equijoins, self-joins, Non-equijoins (come on - who can name the five types of non-equijoins?), cross/outer apply. And they finish off with EXCEPT and INTERSECT, and a small discussion about UNION, UNION ALL, TOP and TABLESAMPLE (that was a new one for me - never had occasion to use it).


...and for those dying for the answer to today's quiz... the five types of non-equijoins are: comparison, inequality, range, conversion, and expression...

In case you're looking for it - it's in the "collection 2778" in MS learning (although that has gotten VERY pricey since I ordered it).


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #437781
Posted Tuesday, January 01, 2008 8:29 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
You must have slept through that part of the 431 prep...


Please tell me they don't actually teach how to use Triangular Joins to derive running totals :P


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #437799
Posted Tuesday, January 01, 2008 10:05 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:12 PM
Points: 7,084, Visits: 14,685
Jeff Moden (1/1/2008)
You must have slept through that part of the 431 prep...


Please tell me they don't actually teach how to use Triangular Joins to derive running totals :P


oops - I quoted a bit too much. I was talking about them actually introducing the concept of joins other than equijoins....(i.e. Gila's comment as to no mention of non equijoins).

And no - I haven't yet run into any part talking about computing running totals.....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #437818
Posted Tuesday, January 01, 2008 11:59 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
Matt Miller (1/1/2008)


You must have slept through that part of the 431 prep...:)



OK, I will admit I didn't do the 431 training. Did do all of the SQL developer training (for exams 441, 442) and I don't recall a discussion of joins



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #437830
Posted Wednesday, January 02, 2008 4:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 28, 2014 1:54 AM
Points: 171, Visits: 229
Lovely example of a coding `performance issue` we found just before christmas...

A collegue here overseas a system with a process they were running that was incredibly slow. (External product we have). Actually, one of a few processes that run slowly with that system ;)

So I showed him the wonder that is `sql server profiler` :) (Which some of our dba's here seem to know little about!)

Spotted a strange pattern in the code being run. 400 instances of count(*) being called when we ran a single record of the process in question, running about 15ms per count(*). In a live situation, this may happen over increasing number of records. Only 100 records? 10 minutes of count(*)'s :)

So we investigate more, and this same code was running all over the place.

We contacted the suppliers and talked to a developer.

....Yes we use count(*) so our system can check if a table exists with a particular copy of our software or not

Im sure you can think of your own series of polite questions you might ask a supplier regarding the above, suffice to say at this point we had some words with them, and await their next update with great interest :)

Makes me think what other wonderments of code they have hidden away in their system!

:)

martin
Post #437903
Posted Wednesday, January 02, 2008 4:48 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
Thanks for the feedback, Martin. Yeah, I have a grand love for such 3rd party shenanigan's myself... my favorite is loading data from a table into a Temp Table, opening a cursor on the Temp Table, and then inserting one row at a time into another table from the cursor with an explicit transaction and no rollback handler. Ya just gotta wonder where some of those folks learned T-SQL.

--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #437911
Posted Wednesday, January 02, 2008 7:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, October 20, 2013 7:52 PM
Points: 1,310, Visits: 483
Great article, Jeff. I've forwarded the link to my colleagues. We all need to be reminded once in a while that, no matter how hard we try, we don't necessarily write perfect code on the first try. Or, in my case, on the tenth try.


Post #437972
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse