Performance Tuning: Concatenation Functions and Some Tuning Myths

Question

Performance Tuning: Concatenation Functions and Some Tuning Myths

Viewing 8 posts - 76 through 82 (of 82 total)

You must be logged in to reply to this topic. Login to reply

JJ B SSCertifiable Points: 5100 More actions · Answer 1

Matt: Great tip! I used it on my earlier post and it worked. Thanks.

Mike C SSC-Insane Points: 23224 More actions · Answer 2

That's what I basically did (after about 10 edit attempts). I italicized everything after the ampersand. It appears that when the parser for this editor sees an ampersand it wipes out all alphabetic characters up to the semicolon. So the trick of using &amp;gt; for instance, wipes out the gt; but converts the &amp; to an ampersand. Seems like it might be a bug in the editor control. Apparently when it encounters a nonalphabetic character like [ it acts as a break between the ampersand and the alphabetic characters.

JJ B SSCertifiable Points: 5100 More actions · Answer 3

Thanks for the additional reply. I wish there was an easy escape character or set of characters to say "hey, please print this text as-shown". Sort of like doubling a single quote in a string to let SQL Server know that it really is just a single quote.

Tom257 SSChampion Points: 11543 More actions · Answer 4

Jeff

Its brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!

I'll be using this, but I'm a bit wary as I don't know exactly how it works

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Tom Brown (8/21/2008)
Jeff
Its brilliant. But how on earth can anyone be expected to figure out the STUFF(... WHERE ... FOR XML PATH('') Syntax - especially when there is no XML involved!
I'll be using this, but I'm a bit wary as I don't know exactly how it works

Thanks... I wish I could take credit for it, but I can't.

I haven't done an indepth analysis of the method, but I've read that it tries to make an XML path out of the individual items and, because the "root" is supressed, it generates not tags. The "STUFF" is just to kill the leading comma.

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

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

John Hanrahan Hall of Fame Points: 3825 More actions · Answer 6

Jeff,

Thanks for the article. I was considering going down the function path when I thought there was a better way. Glad I looked. In my case I am using the 'csv' to provide intelligence data to one of our departments.

John

PhilG Newbie Points: 2 More actions · Answer 7

Then as now. The answer to major performance problems is reduced data sets.

I recently had to recommend this for a Humana open enrollment period. Over a billion records, and an estimated runtime of 21 days.

Broke it up into 100,000 records at a time, and it took 36 hours.

PhilG Newbie Points: 2 More actions · Answer 8

Back in the 80's a friend of mine told me that programmers were taught to code like the processor had infinite clock cycles, and infinite memory. Programing time was expensive, processing time was cheap. I am sure that is still being taught that way.

I came from an electronics background, where everything was about the limits.