23 September 2007 16:18 Alex_Kuznetsov

Mimicking a table variable parameter with an image.

Following a discussion with SQL Server MVP Joe Webb at PASS conference in Denver, I decided to re-post this approach including all the bells and whistles. Of course, the general idea of the approach was borrowed from Erland Sommarskog's article on Arrays and Lists in SQL.

1. Packing an array of numbers in an image. I have to change the order of bytes when I store an array of long integers in an array of bytes, as follows:

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;
This code snippet is also included in Erland's article at
http://www.sommarskog.se/arrays-in-sql-2005.html
 
2. Binding the array of bytes as an image parameter:
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";
}
}
 
3. Objects on SQL Server side:
-- Assuming that there already is an auxiliary Numbers table:
SELECT Number*8 + 1 AS StartFrom, Number*8 + 8 AS MaxLen INTO dbo.ParsingNumbers FROM dbo.Numbers 

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

 

Comments

# Split function | keyongtech

18 January 2009 17:31 by Split function | keyongtech

Pingback from  Split function | keyongtech