Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

Question

Passing Parameters as (almost) 1, 2, and 3 Dimensional Arrays

Viewing 14 posts - 46 through 58 (of 58 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Your recursive CTE will not scale well as the size of the string and number of rows increases. You should take a look at this particular thread, Performance issue with tally solution.

Bill Parrott Old Hand Points: 328 More actions · Answer 2

Very good article! I can't tell you how much time I've spent having to parse delimited in T-SQL. While I haven't gotten all the way through the article, I did find a bug in your one-dimensional processing.

While it wasn't apparent looking at a resultset of 123456 repeated over and over, when I created a string from 100000 to 108000 inclusive I found that it was truncating the first result (100000). The fix was as simple as prefixing the string with a comma.

From: 100000,100001,100002,...,108000,

To: ,100000,100001,100002,...,108000,

Problem solved all the results show up!

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 3

sam.walker (2/14/2009)
-- convert to csv fixed width
set @fixedstr = dbo.fnCsvToFixedClr(@fcsvstr, @fixedlength)

Can you post the fnCsvToFixedClr code? I am curious as to how it may be implemented as well.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Great article again, Jeff! You just can't beat the classics. 😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

jamesburkill-1146441 (1/8/2010)
In SQL Server 2008 you most certainly CAN parse a table variable as a parameter. When used in conjunction with visual studio 2008 you can even parse it from your code.
I often parse XML from my application to my pre-2008 databases but I can see the merit of using this method in 2005 when parsing data from one SP to another.

Thanks for the feedback, James.

I've had folks use it for passing large amounts of data from the GUI to proc. That's usually still a bad idea but the way they were doing it was to pass thousands of INSERT/VALUE statements which took a lot longer and was a whole lot tougher on resources. The idea of passing XML also came up in that instance but there's still a lot more to pass that way than just passing delimited data.

For importing large amounts of data from a file, it's hard to beat BULK INSERT if the file is in good shape.

For passing large amounts of data from proc to proc, I typically don't do that. It's usually easier and provides better performance if the procs are designed to read from tables... even Temp tables (notice I didn't say "Global" Temp Tables).

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

mohd.nizamuddin (1/8/2010)
Excellent article.
BTW I have another version of CTE, which splits delimited string without tally table. I think it works efficiently, also. Please have a look...:-)

Thanks for the feedback, Mohd.

Several heavy hitters on this forum have tested various methods of doing splits of delimited data. There's a common misconception for many people that, because it has no explicitly declared loop, that Recursive CTE's are both set based and fast. Both of the misconceptions are quite untrue. Setup a test table with 100,000 highly variable CSV's in it and give it a try. You'll see what I mean. Actually, 1 row with several thousand elements will normally be good enough.

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

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

Bill Parrott (1/8/2010)
Very good article! I can't tell you how much time I've spent having to parse delimited in T-SQL. While I haven't gotten all the way through the article, I did find a bug in your one-dimensional processing.
While it wasn't apparent looking at a resultset of 123456 repeated over and over, when I created a string from 100000 to 108000 inclusive I found that it was truncating the first result (100000). The fix was as simple as prefixing the string with a comma.
From: 100000,100001,100002,...,108000,
To: ,100000,100001,100002,...,108000,
Problem solved all the results show up!

Thanks for the feedback, Bill. Are you sure that you executed the following line of code which is contained in the "123456" example?

SET @Parameter = ','+@Parameter +','

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

RBarryYoung (1/8/2010)
Great article again, Jeff! You just can't beat the classics. 😀

Thanks, Barry. Always good to hear from you and I hope your back is treating you better than in the past.

Shifting gears, in a separate email, you mentioned that you had some thoughts on triangular joins... if you get a chance and you feel up to it, I'd sure like to hear your thoughts on the subject.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Nice one Jeff. I missed it the first time around (and never searched for anything that pulled it up here at SSC). Adding this one to my reference list.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Jeff Moden (1/9/2010)
RBarryYoung (1/8/2010)
Great article again, Jeff! You just can't beat the classics. 😀
Thanks, Barry. Always good to hear from you and I hope your back is treating you better than in the past.
Shifting gears, in a separate email, you mentioned that you had some thoughts on triangular joins... if you get a chance and you feel up to it, I'd sure like to hear your thoughts on the subject.

Thanks Jeff. As for the other stuff, my laptop ha been beset by viruses, etc. the last three weeks, and has been keeping me offline most of the time. I'll try to catch up with you soon...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

loki1049 SSCrazy Points: 2065 More actions · Answer 11

Wow this is amazing, and I just finished telling my boss I would have too much trouble parsing a comma delimited string using SQL and that the front end guy should do it. In my situation we have a web application that loads in a CSV file and parses it out and loads it into the table, that means the code is stored on the webserver and not very reusible. The goal was to have a generic importer that just fills a table in the database with rows of strings then use SQL to parse it and place it into another temp table for testing data verification before finally inserting it into the actual database table where it belongs. I did a quick look over your situation you set up and made it work for a table of comma delimited strings, and its exactly what I needed, ha if only I had seen this a couple weeks earilier.

Heres my atempt and making this more set based (as you always push for) I didn't fully test it yet, but it does allow for strings of different sizes in the start data, granted this would never happen in my case, or at least shouldn't be happening from a standard CVS input file.

Thanks again Jeff for sharing your knowledge!

--"Monster" Split in SQL Server 2005

--===== Simulate a large CSV parameter of unknown length

DECLARE @Parameter TABLE

(

ID INT IDENTITY(1,1),

Parameter VARCHAR(MAX),

Length int

)

-- This will make a parameter of 56,000 characters for test

-- Note that this will actually make a return of 8001 splits

-- because of the left over trailing comma that I didn't take

-- the time to fix in the example parameter.

INSERT INTO @Parameter (Parameter)

SELECT 'McDonalds,JIM,123123' UNION ALL

SELECT 'Arbys,Dave,666111888' UNION ALL

SELECT 'Wendies,Jill,123,ExtraData,Junk3,'

--===== Add start and end commas to the Parameter so we can handle

-- all the elements the same way

UPDATE @Parameter

SET

Parameter = ',' + Parameter + ',',

Length = LEN(Parameter)+2

;WITH

cteTally AS

(--==== Create a Tally CTE from 1 to whatever the length

-- of the parameter is

SELECT TOP (SELECT MAX(Length) FROM @Parameter)

ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

SELECT

TEMP.ID,

ELEMENT1 = Max(CASE WHEN TEMP.Number = 1 THEN TEMP.VALUE ELSE NULL END),

ELEMENT2 = Max(CASE WHEN TEMP.Number = 2 THEN TEMP.VALUE ELSE NULL END),

ELEMENT3 = Max(CASE WHEN TEMP.Number = 3 THEN TEMP.VALUE ELSE NULL END),

ELEMENT4 = Max(CASE WHEN TEMP.Number = 4 THEN TEMP.VALUE ELSE NULL END),

ELEMENT5 = Max(CASE WHEN TEMP.Number = 5 THEN TEMP.VALUE ELSE NULL END),

ELEMENT6 = Max(CASE WHEN TEMP.Number = 6 THEN TEMP.VALUE ELSE NULL END),

ELEMENT7 = Max(CASE WHEN TEMP.Number = 7 THEN TEMP.VALUE ELSE NULL END)

FROM

(

SELECT

ROW_NUMBER() OVER (PARTITION BY P.ID ORDER BY N) AS Number,

P.ID,

SUBSTRING(P.Parameter,N+1,CHARINDEX(',',P.Parameter,N+1)-N-1) AS Value

FROM cteTally CROSS JOIN @Parameter P

WHERE N < P.Length

AND SUBSTRING(P.Parameter,N,1) = ',' --Notice how we find the comma

)TEMP

GROUP BY

TEMP.ID

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 12

Thanks for the feedback, loki1049, and glad I could help. Nicely done and thanks for sharing your code with us. 🙂

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

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

CirquedeSQLeil (1/9/2010)
Nice one Jeff. I missed it the first time around (and never searched for anything that pulled it up here at SSC). Adding this one to my reference list.

I'm not sure how I missed this post, Jason. :blush: Thanks for stopping by and thanks for the compliment of becoming part of your reference list. 🙂

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

sam.walker Mr or Mrs. 500 Points: 597 More actions · Answer 14

I'd completely forgotten about this chain.

I was going to conduct a trial of the fastest splitter.

I think it was established Clr Table Function was very quick, and the JM's inline function comparable for smaller numbers.

However, I'm sure the solution which combines Clr, and T-SQL tally tables is fastest I've used. It is a 2 stage process. But I'd really like to know, as we use this extensively.

Stage 1. Using Clr convert the delimitted string parameter (of say 20 thousand items) in to a fixed width delimetted string. eg if it is a list of integers, pad each item with say 15 chars.

Stage 2. Use a tally table to split the items using a simple substring(@fixedStr, N * 15 +1, 15)

Can someone please send me the source code of the pure Clr Table Function, so I can conduct some tests?

Sam