SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance Tuning: Concatenation Functions and Some Tuning Myths


Performance Tuning: Concatenation Functions and Some Tuning Myths

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)

Group: General Forum Members
Points: 341015 Visits: 42654
Comments posted to this topic are about the item Performance Tuning: Concatenation Functions and Some Tuning Myths

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul Thornett
Paul Thornett
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 Visits: 96
Not only is the substance of this article excellent, but I also find the style most attractive. It's well-written, extremely useful and also quite amusing. What more could one ask for? If this, the first article I've read in 2008, is representative of what's to come, then already 2008 is looking very promising.



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)

Group: General Forum Members
Points: 341015 Visits: 42654
What a great way to start the year! Thank you for the awesome compliments, 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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ChiragNS
ChiragNS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10225 Visits: 1865
nice one jeff...

"Keep Trying"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)

Group: General Forum Members
Points: 341015 Visits: 42654
Thanks, Chirag. Appreciate the compliment!

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Mike C
Mike C
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9707 Visits: 1172
Nice, as always, Jeff. I notice you said you didn't do any testing on 2005. If you do more testing on 2005, be sure to try out the XML data type with FOR XML for concatenating strings. Here's some timings for the sample below (run on a 1.9 GHz box with 2 GB RAM, SQL 2005):

* Test with PK/CI on RowNum and NCI on SomeID ran in 9 seconds for 1,000,000 rows
* An alternate version using the data() node test as a column name and the REPLACE function instead of SUBSTRING ran in 11 seconds on 1,000,000 rows
* Changing the NCI to a covering index on (SomeID, SomeCode) the code executed in 4 seconds on 1,000,000 rows. I left RowNum out of the covering index create index statement since it's the CI already, so automatically added to the NCI.

Who knows Jeff, maybe XML will find a place in your heart after all? Smile

--Statement to create covering index:
--CREATE INDEX IX_TestData_SomeInt ON dbo.TestData(SomeID, SomeCode)

-- Jeff's table creation code
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeID = ABS(CHECKSUM(NEWID()))%2500+1,
SomeCode = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
INTO dbo.TestData
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 ;

ALTER TABLE dbo.TestData
ADD PRIMARY KEY CLUSTERED (RowNum);

CREATE INDEX IX_TestData_SomeInt ON dbo.TestData(SomeID);
-- End table creation code
GO
-- Begin function creation
CREATE FUNCTION dbo.fnConcatTest (@SomeID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @x XML;
DECLARE @Return VARCHAR(8000);

SET @x = (SELECT ',' AS '*',
SomeCode AS '*'
FROM dbo.TestData
WHERE SomeID = @SomeID
FOR XML PATH (''), TYPE);

SET @RETURN = SUBSTRING(CAST(@x AS VARCHAR(8000)), 2, 8000);
RETURN @Return;
END
GO
--Speed test
SELECT SomeID, dbo.fnConcatTest(SomeID) AS CSVString
FROM dbo.TestData
GROUP BY SomeID;
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)

Group: General Forum Members
Points: 341015 Visits: 42654
Heh... thanks Mike, but you didn't read the code block just before the conclusion Wink Like Ragu... "It's in there". BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19767 Visits: 715
I like the fact you show it's a code issue not a sql issue. I'm constantly asked to index queries to improve them when the reality is that often the underlying code is at fault - I do find less programmers/developers seem to get sent on T-SQL training these days.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Mike C
Mike C
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9707 Visits: 1172
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 Smile

Thanks again Jeff Smile
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)SSC Guru (341K reputation)

Group: General Forum Members
Points: 341015 Visits: 42654
colin Leversuch-Roberts (1/1/2008)
I like the fact you show it's a code issue not a sql issue. I'm constantly asked to index queries to improve them when the reality is that often the underlying code is at fault - I do find less programmers/developers seem to get sent on T-SQL training these days.


Absolutely spot-on, Colin... that's one of the things I'm hoping that folks take away with them after they study the article a bit... doesn't matter what the hardware is and doesn't matter how many indexes you add to a table (well, for Inserts, Updates, and Deletes it might Wink ), poorly written code cannot be saved by table/index "Tuning Tricks"... the code has to be right. It's like painting a rusty car... it may (or may not) look good for a day or two, but unless you take care of the underlying metal, the paint's got no place to stick. Tongue

On the training thing... Not only do I agree with you on the lack of opportunity to be trained, but a good lot of the training doesn't teach folks how to write code with performance in mind. For example, when's the last time you saw some SQL Server training that included something like a Tally table or what a "Triangular Join" actually is? They just don't teach the good stuff in the schools that are supposed to... maybe a seminar here and there but not in most of the "schools".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search