THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Multiple-Valued Parameters

So I was doing some research on which is the best way to pass multiple-valued parameters into a stored procedure or user-defined function when I found some interesting results. I had always used a string-based user-defined function to parse a delimited list of values and I knew I could use XML to do the same thing, yet I had never compared the two side-by-side to see which is a better choice.

So which was faster? Well, using one of many available string-based UDFs, I had unexpected results. I thought that the XML version would be equivalent if not slower, but alas, I was wrong. The XML-based UDF consistently performed about 35% faster than the string-based inline table-valued UDF and about 65% faster than string-based table-valued UDF I tested.

I will be following-up this post soon with a post about various XML-based techniques that I tested…

One last item of note – this post was published (tags and all) from Word 2007.

As for now, here are the various functions used in the tests:

CREATE FUNCTION dbo.fnString2IntList(@sData varchar(8000), @sDelim char(1))
RETURNS @tList TABLE (ListValue int)
AS
BEGIN
    DECLARE @sTemp int, @nPos int, @nPos2 int
    IF (Len(RTrim(LTrim(@sData))) = 0) RETURN

    SET @nPos = CharIndex(@sDelim, @sData, 1)

    IF @nPos = 0 OR @nPos = Len(@sData)
     BEGIN
        SET @sTemp = CAST(SubString(@sData, 1, (Len(@sData) - Sign(@nPos))) as int)
        INSERT INTO @tList VALUES(@sTemp)
        RETURN
     END

    SET @sTemp = CAST(SubString(@sData, 1, @nPos - 1) as int)
    INSERT INTO @tList VALUES(@sTemp)

    WHILE @nPos > 0
     BEGIN    
        SET @nPos2 = CharIndex(@sDelim, @sData, @nPos + 1)
        IF (@nPos2 = 0) SET @sTemp = CAST(SubString(@sData, @nPos + 1, Len(@sData)) as int)
        ELSE SET @sTemp = CAST(SubString(@sData, @nPos + 1, ABS(@nPos2 - @nPos - 1)) as int)

        INSERT INTO @tList VALUES(@sTemp)
        SET @nPos = CharIndex(@sDelim, @sData, @nPos + 1)
     END
    RETURN
END
GO

CREATE FUNCTION dbo.fnString2IntList2(@sData varchar(8000), @sDelim char(1))
RETURNS TABLE
AS
    RETURN
    (WITH csvtbl(i,j)
    AS
    (
        SELECT i = 1, j = CHARINDEX(@sDelim, @sData + @sDelim)
        UNION ALL
        SELECT i = j + 1, j = CHARINDEX(@sDelim, @sData + @sDelim, j + 1)
        FROM csvtbl
         WHERE CHARINDEX(@sDelim, @sData + @sDelim, j + 1) <> 0
    )
    SELECT CAST(SUBSTRING(@sData, i, j - i) as int) AS ListValue
    FROM csvtbl)
GO

/*    Assumes XML is as such
    <list>
        <i>1</i>
        <i>23</i>
    </list>
    etc...

    Uses minimal xml markup to keep input size as small as possible
*/

ALTER FUNCTION dbo.fnXml2IntList(@xmlList xml)
RETURNS TABLE
AS
    RETURN (SELECT tList.ListValue.value('.', 'int') AS ListValue
            FROM @xmlList.nodes('list/i') AS tList(ListValue))
GO

Published Monday, February 12, 2007 4:43 PM by Peter W. DeBetta
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis The SQL Menace said:

Peter, have you tested also with a number table?

Here is an example

-- Create our Number table ** do this only once-- populate it with 1000 rows

CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT

SELECT @intLoopCounter =0

WHILE @intLoopCounter <=999 BEGIN

INSERT INTO NumberPivot

VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1

END

GO

--Just convert this into a function

DECLARE @chvGroupNumbers VARCHAR(1000)

SELECT @chvGroupNumbers ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5,3,2,'

SELECT SUBSTRING(',' + @chvGroupNumbers + ',', NumberID + 1,

CHARINDEX(',', ',' + @chvGroupNumbers + ',', NumberID + 1) - NumberID -1)AS Value

FROM NumberPivot

WHERE NumberID <= LEN(',' + @chvGroupNumbers + ',') - 1

AND SUBSTRING(',' + @chvGroupNumbers + ',', NumberID, 1) = ','

GO

February 12, 2007 5:59 PM
 

David Markle said:

I'm willing to bet you a beer that using CLR integration to create your list would perform just as fast (if not faster) than your XML function.

Are you up for a race?

February 12, 2007 6:55 PM
 

Adam Machanic said:

Sure, scraping the bottom of the barrel and pulling out the worst possible string functions isn't going to yield very good results.  That's to be expected.  Perhaps you should try something better! ... such as:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/splitting-a-string-of-unlimited-length.aspx

... which runs hundreds of times faster than your XML solution for the following test cases:

---

select *

from dbo.splitstring(replicate(convert(varchar(max), '1,'), 100000), ',')

select *

from dbo.fnXml2IntList('<list>' + replicate(convert(varchar(max), '<i>1</i>'), 100000) + '</list>')

---

... actually, I can only assume it's hundreds and not thousands of times.  On my laptop, my function returns in about 2 seconds.  It's been five minutes and I'm still waiting for the XML function to return.  About to hit the stop button...

UPDATE! I hit the stop button and ran both with only 1000 input rows.  Mine returned almost instantly; the XML version took about 4 seconds.

February 12, 2007 10:13 PM
 

Alex Kuznetsov said:

Hi Peter,

The approach I am using is also based on the same great article by Erland Sommarskog that Adam referred to. I am using an image - it has no 8000 bytes limitation. I am using it to transfer up to 10000 8-byte integers (BIGINTS)

I have a table named dbo.ParsingNumbers, it is a Numbers auxiliary table storing values 1,8,17,25 so on.

My function is very short and simple:

ALTER  FUNCTION ParseImageIntoBIGINTs(@BIGINTs IMAGE)

RETURNS TABLE AS RETURN(

 SELECT CAST(SUBSTRING(@BIGINTs, StartFrom, 8) AS BIGINT) Num FROM ParsingNumbers WHERE MaxLen <= DATALENGTH(@BIGINTs))

SELECT * FROM dbo.ParseImageIntoBIGINTs(0x000000000000000100000000000000020000000000000003)

/*

Num                  

--------------------

1

2

3

(3 row(s) affected)

*/

Advantage: an image uses less memory - just 8 bytes for a BIGINT, as compared to 17 bytes if you store it in character format.

What do you think?

February 13, 2007 8:56 AM
 

Greg Linwood said:

That's a very nice solution Alex.

Hvae you tried any comparative timing tests?

Cheers,

Greg

February 13, 2007 3:05 PM
 

Adam Machanic said:

Greg, Erland has comprehensive test results listed in his article.

February 14, 2007 11:13 AM
 

Peter W. DeBetta said:

I concede that for very lengthy inputs (thousands of values in the multiple-valued parameter) that Adam's SplitString function is faster, but when dealing with smaller sets of data, it is not. I tested using one set of 100,000 values in the list and the SplitString is more than an order of magnitude faster (46 milliseconds for SplitString versus 983 milliseconds for Xml2IntList)

I also want to note that passing a string into the XML-based functions adds exponential overhead for the conversion. I would assume that the data was already XML and thus when I tested, I set the input values in variables prior to the call to the function. This greatly changes the performance results Adam observed.

Some test results:

--------------------------------

Number of Items: 100,000

SplitString Time: 46 ms

Xml2IntList Time: 983 ms

--------------------------------

Number of Items: 10,000

SplitString Time: 40 ms

Xml2IntList Time: 93 ms

--------------------------------

Number of Items: 1,000

SplitString Time: 38 ms

Xml2IntList Time: 15 ms

--------------------------------

Number of Items: 100

SplitString Time: 25 ms

Xml2IntList Time: 1.5 ms

--------------------------------

As I said, for larger sets, SplitString is a better choice, but for smaller sets of data, Xml2IntList is faster. So which you should use entirely depends on what is being input.

February 14, 2007 11:39 AM
 

Peter W. DeBetta said:

I also just tested Denis's method and it was faster in every test than the solutions that Adam and I proposed.

Some test results:

--------------------------------

Number of Items: 100,000

SplitString Time: 46 ms

Xml2IntList Time: 983 ms

DenisSplit Time: 2.5 ms

--------------------------------

Number of Items: 10,000

SplitString Time: 40 ms

Xml2IntList Time: 93 ms

DenisSplit Time: 2.5 ms

--------------------------------

Number of Items: 1,000

SplitString Time: 38 ms

Xml2IntList Time: 15 ms

DenisSplit Time: 2.5 ms

--------------------------------

Number of Items: 100

SplitString Time: 25 ms

Xml2IntList Time: 1.5 ms

DenisSplit Time: 0.8 ms

--------------------------------

Number of Items: 10

SplitString Time: 2.5 ms

Xml2IntList Time: 0.2  ms

DenisSplit Time: 0.13 ms

--------------------------------

So if you have a Numbers table, The DenisSplit Function is the way to go! Curse you Denis! :-)

February 14, 2007 12:27 PM
 

Peter W. DeBetta said:

I like Alex's solution a lot. And although I haven't done any perf tests yet, I suspect it would win out. However, it's only applicable to int data. What if the data being split is character data?

February 14, 2007 12:55 PM
 

Denis the SQL Menace said:

>>Curse you Denis! :-)

Not on Valentines Day hopefully ;-)

February 14, 2007 1:14 PM
 

Denis the SQL Menace said:

Peter just out of curiousity did you up the numbers table to 100000 rows when you tested? if you left it at 999 then it will only process the first 999 characters and skip the rest

February 14, 2007 2:01 PM
 

Peter W. DeBetta said:

Denis - I did notice the skewed results when I used a smaller numbers table with a larger set of input data, and so I created a Numbers table with a million numbers.

February 14, 2007 2:30 PM
 

Denis the SQL Menace said:

>> I created a Numbers table with a million numbers.

so this result is for a million number table...not bad at all

February 14, 2007 2:33 PM
 

Alex Kuznetsov said:

Peter and Greg,

Sorry for a delayed reply, I was busy troubleshooting.

According to Erland's article the fastest solution is to use fixed width strings.

http://www.sommarskog.se/arrays-in-sql.html#performance-tests

I did not rerun and/or verify his benchmarkings.

I was interested only in and tested only for large arrays of integers, I did not propose a comprehensive solution.

So I packed 8-byte integers from 1 to 10000 into an IMAGE and into a string (19 bytes per a number) - my C# code is below.

On 2005 for IMAGE I am getting

Duration: 82 - 86

CPU: 70 - 90

For VARCHAR(MAX)

Duration: 118 - 123

CPU: 125

Because on 2005 I can use VARCHAR(MAX) and I do not need to split my string into 8K chunks, the advantage of IMAGE is less pronounced.

The database set up:

USE Sandbox

go

DROP TABLE ParsingNumbers

go

DROP TABLE ParsingNumbersBy19

go

SELECT Number*8 + 1 AS StartFrom, Number*8 + 8 AS MaxLen INTO dbo.ParsingNumbers FROM dbo.Numbers

go

SELECT Number*19 + 1 AS StartFrom, Number*19 + 19 AS MaxLen INTO dbo.ParsingNumbersBy19 FROM dbo.Numbers

go

CREATE TABLE BigintsTarget(bi BIGINT)

go

CREATE TABLE BigintsTarget1(bi BIGINT)

go

CREATE FUNCTION dbo.ParseImageIntoBIGINTs(@BIGINTs IMAGE)

RETURNS TABLE AS RETURN(

 SELECT CAST(SUBSTRING(@BIGINTs, StartFrom, 8) AS BIGINT) Num FROM dbo.ParsingNumbers WHERE MaxLen <= DATALENGTH(@BIGINTs))

GO

CREATE FUNCTION dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs VARCHAR(MAX))

RETURNS TABLE AS RETURN(

 SELECT CAST(SUBSTRING(@BIGINTs, StartFrom, 19) AS BIGINT) Num FROM dbo.ParsingNumbersBy19 WHERE MaxLen <= DATALENGTH(@BIGINTs))

GO

My C# code:

class Class1

{

static byte[] UlongsToBytes(ulong[] ulongs)

{

int ifrom = ulongs.GetLowerBound(0);

int ito   = ulongs.GetUpperBound(0);

int l = (ito - ifrom + 1)*8;

byte[] ret = new byte[l];

int retind = 0;

for(int i=ifrom; i<=ito; i++)

{

ulong v = ulongs[i];

ret[retind++] = (byte) (v >> 0x38);

ret[retind++] = (byte) (v >> 0x30);

ret[retind++] = (byte) (v >> 40);

ret[retind++] = (byte) (v >> 0x20);

ret[retind++] = (byte) (v >> 0x18);

ret[retind++] = (byte) (v >> 0x10);

ret[retind++] = (byte) (v >> 8);

ret[retind++] = (byte) v;

}

return ret;

}

static string UlongsToString(ulong[] ulongs)

{

int ifrom = ulongs.GetLowerBound(0);

int ito   = ulongs.GetUpperBound(0);

int l = (ito - ifrom + 1)*8;

StringBuilder sb = new StringBuilder(l);

for(int i=ifrom; i<=ito; i++)

{

ulong v = ulongs[i];

sb.AppendFormat("{0,19}",v);

}

return sb.ToString();

}

static string UlongsToDirectInsert(ulong[] ulongs)

{

int ifrom = ulongs.GetLowerBound(0);

int ito   = ulongs.GetUpperBound(0);

StringBuilder sb = new StringBuilder("INSERT INTO BigintsTarget(bi) SELECT Number FROM Numbers WHERE Number IN(");

string commaAfter1st = "";

for(int i=ifrom; i<=ito; i++)

{

ulong v = ulongs[i];

sb.Append(commaAfter1st);

commaAfter1st = ",";

sb.Append(v.ToString());

}

sb.Append(")");

return sb.ToString();

}

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main(string[] args)

{

DateTime d1, d2, d3;

d1 = DateTime.Now;

d2 = DateTime.Now;

string sddd = d1.ToString();

ulong[] ul = new ulong[10000];

for(uint i=0; i<10000; i++)

{

ul[i] = i;

}

string sss = UlongsToString(ul);

byte[] ba = UlongsToBytes(ul);

string directInsert = UlongsToDirectInsert(ul);

try

{

string source = @"packet size=4096;integrated security=SSPI;data source=MyPC\MyNamedInstance;persist security info=False;initial catalog=Sandbox";

SqlConnection conn = new SqlConnection(source);

conn.Open();

SqlCommand a = new SqlCommand("INSERT BigintsTarget(bi) SELECT * FROM dbo.ParseImageIntoBIGINTs(@BIGINTs)", conn);

a.CommandType = System.Data.CommandType.Text;

a.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.Image,2147483647));

for(int q=0; q<10; q++)

{

a.Parameters[0].Value = ba;

int res = a.ExecuteNonQuery();

}

d2 = DateTime.Now;

SqlCommand b = new SqlCommand("INSERT BigintsTarget1(bi) SELECT * FROM dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs)", conn);

b.CommandType = System.Data.CommandType.Text;

b.Parameters.Add(new SqlParameter("@BIGINTs", System.Data.SqlDbType.VarChar,2147483647));

for(int q=0; q<10; q++)

{

b.Parameters[0].Value = sss;

int res = b.ExecuteNonQuery();

}

//b.ExecuteNonQuery();

conn.Close();

}

catch(Exception ex)

{

string s = ex.Message;

int t=0;

t++;

}

d3 = DateTime.Now;

string sdiff1 = d1.ToString() + " - " + d2.ToString();

string sdiff2 = d2.ToString() + " - " + d3.ToString();

string tttttt = "sdfa";

}

}

Testing that it worked correclty:

SELECT COUNT(*), MIN(bi), MAX(bi), COUNT(DISTINCT bi) FROM dbo.BigintsTarget

SELECT COUNT(*), MIN(bi), MAX(bi), COUNT(DISTINCT bi) FROM dbo.BigintsTarget1

It would be interesting to extend this benchmarking and compare other methods mentioned in this discussion, but I cannot continue at this this time.

-----------------------

Alex Kuznetsov

http://sqlserver-tips.blogspot.com/

http://sqlserver-puzzles.blogspot.com/

February 15, 2007 9:39 AM
 

Peter W. DeBetta said:

I read Erland's article (http://www.sommarskog.se/arrays-in-sql.html) and suggest everyone who hasn't also read it.

The fixed-width solution certainly has its merits, especially for integer data. I wonder how easy it would be to implement a fixed-width string-based solution, from both the database and client application?

I also would have preferred a non-Perl based testing solution (since I selfishly don't want to install yet another service/application on my laptop). Erland's source code is available, however, so if time permits, I'm going to develop a .NET version of the tests. But before I do, I'm going to wait and see if Erland first will do a follow up to his article in the near future (hint, hint, Erland).

February 15, 2007 11:03 AM
 

Alex Kuznetsov said:

Peter,

On 2005 with its new VARCHAR(MAX) type a fixed-width string-based solution is just as simple as an image-based one:

CREATE FUNCTION dbo.ParseVarcharMAXIntoBIGINTs(@BIGINTs VARCHAR(MAX))

RETURNS TABLE AS RETURN( SELECT CAST(SUBSTRING(@BIGINTs, StartFrom, 19) AS BIGINT) Num FROM dbo.ParsingNumbersBy19 WHERE MaxLen <=DATALENGTH(@BIGINTs))

We don't have to split a text into 8000 bytes chunks any more, so it is much simpler. Composing a string of fixed-width numbers on C# client is just as simple:

static string UlongsToString(ulong[] ulongs)

{

int ifrom = ulongs.GetLowerBound(0);

int ito   = ulongs.GetUpperBound(0);

int l = (ito - ifrom + 1)*8;

StringBuilder sb = new StringBuilder(l);

for(int i=ifrom; i<=ito; i++)

{

ulong v = ulongs[i];

sb.AppendFormat("{0,19}",v);

}

return sb.ToString();

}

What do you think?

February 15, 2007 10:04 PM
 

Phil Factor said:

I've just run some tests using the XML solution and Adam's TEXT splitting routine, and I consistently get the XML routine running much faster. I'm running it on a list of all the pubs in Cambridge (UK), using either a list with a single delimiter or as XML. I've run it against the quickest VARCHAR splitting routine I know of (which uses the number 'helper' table) , which generally takes about half the time of Adam's.

Phil

March 8, 2007 8:35 AM
 

Wendell said:

I was doing this with MS CRM and ran into a real problem. We have 172 user (this is a subset - only account execs) that I wanted the user running the report to be able to select. Actually, this was requested. Being a huge technical report, I decided to use a stored procedure and pass in a string of the ids as I have seen examples like your article. Well...  the problem is that MS CRM uses GUIDs for the ids which are 36 characters long when converted to a string. So 272 * 36 = 9,792 characters. Hmmm... What SQL Server data type can handle that and can be passed in as an stored procedure parameter. I believe the answer in none. If when 'all' is selected in a MultiValue list, they would send an asterisk or something, I could have managed this, But because they send everything, I cannot.

February 9, 2009 3:59 PM
 

Chris K said:

Wendell Varchar(max) can handle about 2gb of data so you can use that data type in SQl server 2005 +.

June 25, 2009 9:46 AM

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement