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

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?

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.

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?

That's a very nice solution Alex.

Hvae you tried any comparative timing tests?

Cheers,

Greg

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

>>Curse you Denis! :-)

Not on Valentines Day hopefully ;-)

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

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

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

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/

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?

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