Performance Tuning: Concatenation Functions and Some Tuning Myths

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Performance Tuning: Concatenation Functions...

80 posts, Page 1 of 8

1 2 3 4 5 »»»

Performance Tuning: Concatenation Functions and Some Tuning Myths

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Monday, December 31, 2007 9:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #437688

Paul Thornett

Posted Monday, December 31, 2007 9:09 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 07, 2011 8:38 AM
Points: 28, 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.

Post #437689

Jeff Moden

Posted Monday, December 31, 2007 10:09 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

What a great way to start the year! Thank you for the awesome compliments, Paul!

--Jeff Moden

Post #437690

ChiragNS

Posted Monday, December 31, 2007 10:38 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825

nice one jeff...

"Keep Trying"

Post #437692

Jeff Moden

Posted Monday, December 31, 2007 10:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

Thanks, Chirag. Appreciate the compliment!

--Jeff Moden

Post #437693

Mike C

Posted Monday, December 31, 2007 11:53 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

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? :)

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

Post #437698

Jeff Moden

Posted Tuesday, January 01, 2008 12:08 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

Heh... thanks Mike, but you didn't read the code block just before the conclusion ;) Like Ragu... "It's in there". :D

--Jeff Moden

Post #437700

colin.Leversuch-Roberts

Posted Tuesday, January 01, 2008 10:22 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

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/

Post #437745

Mike C

Posted Tuesday, January 01, 2008 10:58 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:22 PM
Points: 1,276, Visits: 1,112

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 :)

Post #437750

Jeff Moden

Posted Tuesday, January 01, 2008 1:02 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

colin Leversuch-Roberts (1/1/2008)

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 ;) ), 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. :P

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

Post #437765

« Prev Topic | Next Topic »

80 posts, Page 1 of 8

1 2 3 4 5 »»»

Permissions