How can I speed up this C # / SQL function?

Question

This code serializes an array of integers and then inserts it into the sql table. It is not as fast as I need. Can I do something more efficiently?

Thanks!

public void SetItem(long Id, int[] items) { using (MemoryStream stream = new MemoryStream()) { foreach (int d in items) { var bin = BitConverter.GetBytes(d); //Serialize stream.Write(bin, 0, bin.Length); } var array = stream.ToArray(); using (SqlCommand cmd = new SqlCommand("INSERT INTO Items(Id, Item, DateCreated) VALUES (@Id, @binaryValue, @dateCreated)", sqlConnection)) { cmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, array.Length).Value = array; cmd.Parameters.Add("@Id", SqlDbType.BigInt).Value = Id; cmd.Parameters.Add("@dateCreated", SqlDbType.DateTime2).Value = DateTime.Now; cmd.ExecuteNonQuery(); } } } 

Answer 1

I advise you to split this function into two parts. One of them concerns byte array - another for insertion into the database.

Then start profiling and see if the byte array is slow or there is a problem with db.

Maybe you are trying to speed up something that is not slow :)

Answer 2

If you insert many rows, the SqlBulkCopy class SqlBulkCopy much faster than calling insert many times. See this blog post for an example .

Answer 3

The first thing I would like to try is the byte[] preassolation for the memory stream:

 var array = new int[BitConverter.GetBytes(0).Length * items.Length]; using (MemoryStream stream = new MemoryStream(array)) { // ... rest is almost the same } 

Answer 4

You can create a procedure using the insert command. This is faster because the procedure is already compiled for Sql

Something like that:

  SqlConnection conn = new SqlConnection(actual_string); conn.Open(); // Create the command string SqlCommand cmd = new SqlCommand("EXEC insert_test @var1, @var2, @var3, @str1, @str2", conn); // Iterate through all of the objects try { for (int i = 0; i < 10000; i++) { cmd.Parameters.Clear(); cmd.Parameters.Add(new SqlParameter("@var1", var1)); cmd.Parameters.Add(new SqlParameter("@var2", var2)); cmd.Parameters.Add(new SqlParameter("@var3", var3)); cmd.Parameters.Add(new SqlParameter("@str1", str1)); cmd.Parameters.Add(new SqlParameter("@str2", str2)); // Read in all the data cmd.ExecuteNonQuery(); } } finally { conn.Close(); } 

But my preference is to send XML to the procedure.

You can see more in this good article.

Answer 5

My first caveat was to pre-allocate the array that MemoryStream will use, and then use BinaryWriter to write to it:

 var OutputArray = new byte[items.Length * 4]; using (var ms = new MemoryStream(OutputArray)) { using (var writer = new BinaryWriter(ms)) { foreach (var i in items) { writer.Write(i); } } } // You can now send the OutputArray to SQL server 

BinaryWriter does not use BitConverter.GetBytes internally. Rather, it extracts the bytes one at a time from the int and puts them in a buffer. Then the buffer is written to the stream. BitConverter , on the other hand, allocates a new 4-byte buffer every time you call it.