Comma separated Value

Question

Comma separated Value

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Mark-101232 (10/7/2009)
Matt Whitfield (10/7/2009)
Ahh I see - interesting he didn't update the article 😀
I see results on my boxes as being faster for 2000 & 2005 and equitable on 2008, using that method.
Interestingly, though, I see ISNULL being a lot faster in actual use - for example...
WITH Test AS (SELECT TOP 1000000 CONVERT([bigint], [ap].[object_id]) AS object_id FROM [sys].[all_parameters] [ap] CROSS JOIN [sys].[all_parameters] [ap1])
SELECT SUM(COALESCE([Test].[object_id], 0)) FROM [Test]
is consistently slower than
WITH Test AS (SELECT TOP 1000000 CONVERT([bigint], [ap].[object_id]) AS object_id FROM [sys].[all_parameters] [ap] CROSS JOIN [sys].[all_parameters] [ap1])
SELECT SUM(ISNULL([Test].[object_id], 0)) FROM [Test]
on both my 2005 and 2008 rigs...
One of those arguments for 'test, test and test again'
Yep, I seem to get the same results for 2005/2008 - your query runs quicker with ISNULL, but the queries from the article run quicker with COALESCE on 2008 and pretty much the same with 2005.
Hmm, explanations anyone?

I have problems with code of that nature (ie: the While loops used in the article). First, are you really testing the item under test or are you testing a While Loop? 2nd... printing in a While Loop will also take a fair bit of time. Yes, all the items under test share the same problem but the differences between the two becomes smaller because of the large amount of time the While Loop and PRINT statements take. I've also seen where the use of such While Loop testing can appear to reverse simple based on what else the computer is doing because they take so long to run.

--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)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

@Anitha,

I'd still like to know why you need to do such a thing... it's just a matter of wanting to know on my part and, maybe, I (or someone else) might know a high speed alterative to solve your problem.

--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)
Intro to Tally Tables and Functions

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 3

how about...

SELECT [Empid] + ','

FROM yourTable

WHERE LEN([Empid])>0 AND [Empid] IS NOT NULL

FOR XML PATH('')

is that method any faster?

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 4

...or to remove the trailing comma:

DECLARE @emp VARCHAR(MAX)

SELECT @emp = CAST

(

SELECT [Empid] + ','

FROM yourTable

WHERE LEN([Empid])>0 AND [Empid] IS NOT NULL

FOR XML PATH('')

)

AS VARCHAR(MAX)

)

SELECT LEFT(@emp, LEN(@emp) - 1)

disclaimer: I found part of the above code elsewhere on the net and adapted it 🙂

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

I've not testest this particular code but it should be close. It's a copy of the code above with a few "speed" tricks added...

DECLARE @emp VARCHAR(MAX)

SELECT @emp = STUFF --We always know where the first comma is

(

SELECT ',' + [Empid]

FROM yourTable

WHERE EmpID > '' --Not blank and not null

--WHERE LEN([Empid])>0 AND [Empid] IS NOT NULL

FOR XML PATH('')

)

,1,1,''

)

SELECT @Emp

--SELECT LEFT(@emp, LEN(@emp) - 1)

--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)
Intro to Tally Tables and Functions

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 6

Thanks for the additional 'speed tricks' Jeff... 🙂

Btw - how does the performance of the 'FOR XML' method of extraction compare with other methods?

Also, the last line of the above code should read @emp not @Emp

just a typo I know 😉

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Ivanna Noh (10/11/2009)
Thanks for the additional 'speed tricks' Jeff... 🙂
Btw - how does the performance of the 'FOR XML' method of extraction compare with other methods?
Also, the last line of the above code should read @emp not @Emp
just a typo I know 😉

Heh... it's only a typo if you have a case sensitive server... I don't and hope never to have one.

So far as performance goes, take a look at the following post...

http://qa.sqlservercentral.com/Forums/Topic802508-1672-2.aspx#BM802935

--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)
Intro to Tally Tables and Functions