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 Monday, December 31, 2007 9:07 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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."

"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 #437688
Posted Monday, December 31, 2007 9:09 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Monday, December 31, 2007 10:09 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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."

"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 #437690
Posted Monday, December 31, 2007 10:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 1:27 AM
Points: 2,366, Visits: 1,837
nice one jeff...


"Keep Trying"
Post #437692
Posted Monday, December 31, 2007 10:40 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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."

"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 #437693
Posted Monday, December 31, 2007 11:53 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 1,276, Visits: 1,132
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
Posted Tuesday, January 01, 2008 12:08 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
Heh... thanks Mike, but you didn't read the code block just before the conclusion ;) Like Ragu... "It's in there". :D

--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 #437700
Posted Tuesday, January 01, 2008 10:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 2,674, Visits: 694
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
Posted Tuesday, January 01, 2008 10:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:18 PM
Points: 1,276, Visits: 1,132
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
Posted Tuesday, January 01, 2008 1:02 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 5:41 PM
Points: 35,944, Visits: 30,229
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 ;) ), 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
"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 #437765
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse