Alex_Kuznetsov

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

Just a very brief example:

1. Create 10K stored procedures. 

DECLARE @i INT, @SQL NVARCHAR(300)
SELECT @i = 0
WHILE @i < 10000 BEGIN
  SET @i = @i + 1
  SET @SQL = 'CREATE PROCEDURE dbo.DummyProc' + CAST(@i AS NVARCHAR(10)) + ' AS RETURN 0'
  EXEC sp_executesql @SQL
END
GO

 2. Run them.

DECLARE @i INT, @j INT, @d DATETIME, @SQL NVARCHAR(300), @SQL_to_run NVARCHAR(300)
SELECT @i = 0, @j = 0, @d = getdate()
WHILE @j < 3 BEGIN
  SET @j = @j + 1
  IF @j = 1 BEGIN
    SET @SQL = 'EXEC DummyProc'
  END ELSE BEGIN
    SET @SQL = 'EXEC dbo.DummyProc'
  END
  WHILE @i < 10000 BEGIN
    SET @i = @i + 1
    SET @SQL_to_run = @SQL + CAST(@i AS NVARCHAR(10))
    --PRINT @SQL_to_run
    IF @j <3 BEGIN
      EXEC sp_executesql @SQL_to_run
    END
  END
  SELECT DATEDIFF(ms, @d, GEtdate()) as ms
  SELECT @i = 0, @d = getdate()
END
GO

 3. Clean up.

DECLARE @i INT, @SQL NVARCHAR(300)
SELECT @i = 0
WHILE @i < 10000 BEGIN
  SET @i = @i + 1
  SET @SQL = 'DROP PROCEDURE dbo.DummyProc' + CAST(@i AS NVARCHAR(10))
  EXEC sp_executesql @SQL
END
GO

The results:

ms - no schema names
-----------
3860

(1 row(s) affected)

ms - with schema names
-----------
640

(1 row(s) affected)

ms - the overhead of running the loop without sp_executesql calls
-----------
46

 However, if you repeatedly call one and the same procedure, you will not notice much difference one way or another:

CREATE PROCEDURE dbo.DummyProc
AS
RETURN 0
GO
CREATE PROCEDURE dbo.Run_SQL_100K_times
  @SQL NVARCHAR(30)
AS
DECLARE @i INT, @d DATETIME
SELECT @i = 0, @d = getdate()
WHILE @i < 100000 BEGIN
  SET @i = @i + 1
  EXEC sp_executesql @SQL
END
SELECT DATEDIFF(ms, @d, GEtdate()) as ms, @SQL AS SQL
GO
EXEC dbo.Run_SQL_100K_times 'EXEC DummyProc'
EXEC dbo.Run_SQL_100K_times 'EXEC dbo.DummyProc'
GO
DROP PROCEDURE dbo.Run_SQL_100K_times
DROP PROCEDURE dbo.DummyProc
GO

ms          SQL
----------- ------------------------------
2330        EXEC DummyProc

(1 row(s) affected)

ms          SQL
----------- ------------------------------
2310        EXEC dbo.DummyProc

(1 row(s) affected)

"Close These Loopholes - Testing Database Modifications" 

http://www.simple-talk.com/sql/t-sql-programming/close-these-loopholes---testing-database-modifications/