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

Is there an joiner much like the 8kSplitter? Expand / Collapse
Author
Message
Posted Thursday, July 26, 2012 8:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 5,218, Visits: 5,072
I'm a fan of Jeff's 8k Splitter function, works a charm, but we have an application which feeds in a single column with a list of comma seperated values.

Currently a developer has written a function with a cursor looping round building the string, but it is shockingly bad on performance.

So just wondering if there is an inverse of the splitter which is speedy.

This is the function code, table and column names changed as its senstive information

	DECLARE @SID INT
DECLARE @descr varchar(255)
DECLARE @item varchar(255)

DECLARE cDetailsCursor CURSOR
READ_ONLY
FOR

select csg.CID
from table1 csg
JOIN table2 map
ON map.ID = csg.ID
WHERE map.SID = @SID

union

select cs.CID
FROM table3 cs
where cs.SID = @SID

OPEN cDetailsCursor

SET @descr = ''

FETCH NEXT FROM cDetailsCursor INTO @item

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)
BEGIN
IF Len(@descr) = 0
BEGIN
SET @descr = @item
END
ELSE
BEGIN
SET @descr = @descr + ', ' + @item
END

END

FETCH NEXT FROM cDetailsCursor INTO @item
END

CLOSE cDetailsCursor
DEALLOCATE cDetailsCursor

RETURN(@descr)

The cursor brings back a list of INT's

eg
1
2
3
4
5
6

and outputs
'1, 2, 3, 4, 5, 6'

The select outputs at the moment 4286808 rows without the where clauses, there can be any number of CID's assigned to a SID so I am thinking it might have to be a dynmaic unpivot as one SID might have 10 CID's and another may have 3000000 CID's.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1335869
Posted Thursday, July 26, 2012 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:18 AM
Points: 5,315, Visits: 9,736
Anthony

Just declare a variable and concatenate in one single SELECT statement, something like this (not tested):

SET @MyVariable = ''

SELECT @MyVariable = @MyVariable + ', ' + MyColumn
FROM MyTable
ORDER BY MyKeyColumn

-- Remove leading comma
SET @MyVariable = RIGHT(@MyVariable ,LEN(@MyVariable )-1)

SELECT @MyVariable

John

Edit: I know I haven't declared the variable - something in the websweeper here won't allow that statement.
Post #1335880
Posted Thursday, July 26, 2012 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 7,219, Visits: 13,684
CROSSTAB and FOR XML PATH would be the most popular around here, there are loads of examples on ssc. FWIW there's a similar thread here.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1335883
Posted Thursday, July 26, 2012 8:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 5,218, Visits: 5,072
Thanks Gents, been one of them days and nothing gone right so far, so my heads not in the game.

Gone with John's responce as its the simpler to impliment, just changed the -1 to a -2 to remove the comma and the space.

Cheers again gents.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1335904
Posted Thursday, July 26, 2012 9:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 23,286, Visits: 32,011
Here is another way to it:


WITH CTE AS
(
SELECT
name as TableName,
object_id
FROM sys.tables WHERE schema_id = schema_id('dbo')
)
SELECT TableName,
CommaList = STUFF((
SELECT ',' + c.name
FROM sys.columns c
WHERE c.object_id = CTE.object_id
ORDER BY c.column_id
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY Tablename;





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1335929
Posted Thursday, July 26, 2012 10:56 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:40 PM
Points: 945, Visits: 1,771
anthony.green (7/26/2012)
Thanks Gents, been one of them days and nothing gone right so far, so my heads not in the game.

Gone with John's responce as its the simpler to impliment, just changed the -1 to a -2 to remove the comma and the space.

Cheers again gents.


if you are going to be using it for a function i would have a look at lynn's STUFF with FOR XML PATH as that could be put together into a iTVF. get an even bigger performance boost over the cursor.

EDIT: after looking at who i was responding to i am willing to hazard a guess that anthony knows that all ready.



For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1335989
Posted Thursday, July 26, 2012 11:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 7,139, Visits: 15,187
anthony.green (7/26/2012)
Thanks Gents, been one of them days and nothing gone right so far, so my heads not in the game.

Gone with John's responce as its the simpler to impliment, just changed the -1 to a -2 to remove the comma and the space.

Cheers again gents.


Be sure to handle nulls appropriately (something STUFF does for you). If you don't any one null entry in the table will NULL out the entire list.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1335996
Posted Friday, July 27, 2012 1:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 5,218, Visits: 5,072
Thanks all.

With John's solution the view complete its run in 7 minutes 7 seconds and didnt max out TempDB.

I have had to change Lynn's solution slightly
	DECLARE @descr VARCHAR(MAX) = '', @SID INT = 10
;WITH CTE AS
(
SELECT
csg.CID
FROM
table1 csg
JOIN
table2 map
ON
map.PID = csg.PID
WHERE
map.SID = @SID
UNION
SELECT
cs.CID
FROM
table3 cs
WHERE
cs.SID = @SID
),
CTE2 AS(
SELECT CommaList = STUFF((
SELECT ', ' + CONVERT(VARCHAR,c.CID)
FROM CTE c
ORDER BY c.ID
FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,2,'')
FROM CTE
)
SELECT @descr = CommaList FROM CTE2 GROUP BY CommaList

RETURN (@descr)

Wrapped the STUFF into another CTE due to it duplicating the row for how ever many CID's there are, so that can do a group by to just return the 1 distinct row.

Currently running it based on Lynn's solution.

Matt, luckly enough the columns in the select are primary keys so I dont need to worry about encountering a NULL value, but thanks for the reminder.

Edit, doh forgot to change the return type, thats now been done to return a table so its iTVF and not scalar.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1336291
Posted Friday, July 27, 2012 2:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:53 AM
Points: 5,218, Visits: 5,072
John's solution just run again took 8mins 8 seconds

Lynn's solution as used in a iTVF took 18 minutes 26 seconds.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1336304
Posted Friday, July 27, 2012 2:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 7,219, Visits: 13,684
anthony.green (7/27/2012)
John's solution just run again took 8mins 8 seconds

Lynn's solution as used in a iTVF took 18 minutes 26 seconds.


How many rows are you working with? It would be interesting to see how the cross-tab method compares.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1336307
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse