Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing a list of values to a Stored Procedure (Part III)

T-SQL Hints and Tips

Passing a list of values to a Stored Procedure (Part III)

by  ESquared  Posted    (Edited  )
First, see [link http://www.tek-tips.com/faq.cfm?pid=183]Passing a list of values to a Stored Procedure (Part II)[/link]. I am adding part 3 to give you more options, especially to handle

[li]Cases where the input variable is of text datatype and perhaps extremely long[/li]
[li]Cases where the input tokens are of known length or of a relatively low maximum length[/li]
[li]The use of a numbers table instead of a loop[/li]

So here you go.

First, create a Numbers table

If you don't have a Numbers table, here's some code for you to create one. Note that there are debates about the use of this technique. In many situations, using a Numbers table can be a sign of sloppinessùbut not always. See option 6 for some notes about improved performance. Note that the clustered primary key in this table is absolutely essential to its proper performance. I pick 8000 numbers for arbitrary reasons, to have a good mix of small size (speed) and range of numbers (too small and I can't do the work I need to do).

Code: 
CREATE TABLE Numbers (Num int identity(1, 1) NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED)
SET NOCOUNT ON
INSERT Numbers DEFAULT VALUES
WHILE Scope_Identity() < 8000 INSERT Numbers DEFAULT VALUES
[li]Option 5 - use a Numbers table[/li]

Now that you have a Numbers table, here's a function that uses it to split a string into columns.

Code: 
[blue]CREATE FUNCTION[/blue] SplitNumbers [gray]([/gray]
   @InputText [blue]varchar[/blue][gray]([/gray]8000[gray]),[/gray]
   @Delimiter [blue]varchar[/blue][gray]([/gray]1[gray])[/gray]
[gray])[/gray]
[blue]RETURNS TABLE
AS
RETURN [/blue][gray]([/gray]
   [blue]SELECT[/blue]
      [color #007f7f]--This first column will drastically hurt performance[/color]
      TokenID [gray]=[/gray] Num [gray]-[/gray] [color #ff40ff]Datalength[/color][gray]([/gray]Replace[gray]([/gray][color #ff40ff]Left[/color][gray]([/gray]@InputText, Num [gray]-[/gray] 1[gray]),[/gray] @Delimiter, [color #ff40ff]''[/color][gray])),[/gray]
      Value [gray]=[/gray] [color #ff40ff]Substring[/color][gray]([/gray]@InputText, Num, [color #ff40ff]CharIndex[/color][gray]([/gray]@Delimiter, @InputText [gray]+[/gray] @Delimiter, Num) [gray]-[/gray] Num[gray])[/gray]
   [blue]FROM[/blue] Numbers
   [blue]WHERE[/blue] 
      [color #ff40ff]Substring[/color][gray]([/gray]@Delimiter [gray]+[/gray] @InputText[gray],[/gray] Num[gray],[/gray] 1[gray]) =[/gray] @Delimiter
      [blue]AND [/blue] Num [gray]<=[/gray] [color #ff40ff]Datalength[/color][gray]([/gray]@InputText[gray]) +[/gray] 1
[gray])[/gray]
If you compare this to option 4 from the previous FAQ, you'll find that it's much faster for longer strings (some testing is in order for short strings).

But after a lot of research on this topic more than two years ago, I found an article that tested performance of all the various ways of splitting a string into rows. (Yes, it took me long enough to come write this FAQ. I'll try to find that article and if it's still out there will provide a link.) The clear winner was to use a Numbers table against fixed-length inputs. The idea is instead of submitting a string '1,2,7,42' with delimiters between the values, the client application submits them with padding so they are all the same width: for example '1 2 7 42 ' could be an input string with a token length of 3 characters for each number.

One problem with the numbers table though is, what if you want to use a text variable with extremely long lists of numbers? Let's say you're using positive integers, which in SQL Server can be ten characters long. In an 8000-character string, that's only 800 values. It's not a stretch to imagine a client needing to work with 800 rows at once, for example an orders table could easily have this many for just one day.

So combining the fixed-length technique with a cross-join of the numbers table against itself (to handle up to 64,000,000 characters in the case of an 8000 number table), here is

[li]Option 6 - Numbers table with fixed-length tokens[/li]

So here is the culmination of my search for the absolutely fastest split function in SQL Server that can handle very long strings. Make sure you have a Numbers table (see the code above if you don't). It can have any number of numbers in it, but note that if you have n numbers then the maximum length of a string you can handle is n[sup]2[/sup].

Code: 
[blue]CREATE FUNCTION[/blue] SplitFixed [gray]([/gray]
   @InputText [blue]text[/blue][gray],[/gray]
   @TokenLength [blue]tinyint[/blue]
[gray])[/gray]
[blue]RETURNS TABLE
AS
RETURN [/blue][gray]([/gray]
   [blue]SELECT[/blue]
   TokenID [gray]=[/gray] N1.Num [gray]+[/gray] MaxNum [gray]* ([/gray]N2.Num [gray]-[/gray] 1[gray]),[/gray]
   Value =
         SubString[gray]([/gray]
            @InputText[gray],[/gray]
            @TokenLength [gray]* ([/gray]N1.Num [gray]+[/gray] MaxNum [gray]* ([/gray]N2.Num [gray]-[/gray] 1[gray])[/gray] [gray]-[/gray] 1[gray]) +[/gray] 1[gray],[/gray]
            @TokenLength
         [gray])[/gray]
   [blue]FROM[/blue]
      Numbers N1
      [gray]CROSS JOIN ([/gray]
         [blue]SELECT[/blue] MaxNum [gray]=[/gray] Max[gray]([/gray]Num[gray])[/gray] [blue]FROM[/blue] Numbers
      [gray])[/gray] M
      [gray]JOIN[/gray] Numbers N2 ON
         @TokenLength [gray]* ([/gray]M.MaxNum [gray]* ([/gray]N2.Num [gray]-[/gray] 1[gray]) +[/gray] N1.Num [gray]-[/gray] 1[gray]) +[/gray] 1 [gray]<=[/gray] [color #ff40ff]DataLength[/color][gray]([/gray]@InputText[gray])[/gray]
   [blue]WHERE[/blue]
      N2.Num [gray]<=[/gray] [color #ff40ff]DataLength[/color][gray]([/gray]@InputText[gray]) / ([/gray]MaxNum [gray]*[/gray] @TokenLength[gray]) +[/gray] 1
      [gray]AND[/gray] N1.Num [gray]<=[/gray]
         [color #ff40ff]CASE[/color]
         [blue]WHEN[/blue] [color #ff40ff]DataLength[/color][gray]([/gray]@InputText[gray]) /[/gray] @TokenLength [gray]<=[/gray] MaxNum
            [blue]THEN[/blue] [color #ff40ff]DataLength[/color][gray]([/gray]@InputText[gray]) /[/gray] @TokenLength
               [gray]+[/gray] [color #ff40ff]CASE DataLength[/color][gray]([/gray]@InputText[gray]) %[/gray] @TokenLength
               [blue]WHEN[/blue] 0 [blue]THEN[/blue] 0
               [blue]ELSE[/blue] 1
               [blue]END[/blue]
         [blue]ELSE[/blue] MaxNum
      [blue]END[/blue]
   [gray])[/gray]
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top