Call sqlCommand in a loop, increasing the execution time of each step

Question

I have a loop that executes a stored procedure in a loop with over 40,000 iterations, for example:

SqlCommand command = new SqlCommand("WriteDataToDB"); command.Connection = _connection; command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@SignalID", SqlDbType.Int).Value = Arg_Signal.SignalID; command.Parameters.Add("@SignalStrength", SqlDbType.Float).Value = Arg_Signal.SignalSiggestion; command.Parameters.Add("@Time", SqlDbType.BigInt).Value = Arg_Signal.TimeWasHit; command.Parameters.Add("@Value", SqlDbType.Float).Value = Arg_Signal.ValueWasHit; if (command.Connection.State != ConnectionState.Open) { command.Connection.Open(); } command.ExecuteNonQuery(); 

This code is called from a loop where I intercept and time every 1000th iteration. The time I get is below:

[0]: "Started 0ms" [1]: "1000 done 578.125ms"

[2]: "1000 done 921.875ms"

[3]: "1000 done 1328.125ms"

[4]: "1000 done 1734.375ms"

[5]: "1000 done 1140.625ms"

[6]: "1000 done 1250 ms"

[7]: "1000 done 1703.125ms"

[8]: "1000 done 1718.75ms"

......

[31]: "1000 done 3234.375ms"

[32]: "1000 done 3390.625ms"

[33]: "1000 done 3453.125ms"

[34]: "1000 done 3609.375ms"

[35]: "1000 done 3765.625ms"

[36]: "1000 done 3796.875ms"

[37]: "1000 done 3968.75ms"

[38]: "1000 done 4093.75ms"

[39]: "1000 done 4203.125ms"

[40]: "1000 done 4546.875ms"

[41]: "1000 done 4406.25ms"

[42]: "Stopped with the sum of 101093.75ms 1515.625ms"

Does anyone have an idea why these runtimes are increasing? I need to run this code with more than a million iterations - the pace it is going to take a minute to complete one iteration ...

Many thanks

Answer 1

Is there any special logic in your stored procedure or are you just pasting it into the table.

If there is no special logic, or you can do this logic in .NET, then look at the implementation of Bulk Insert . You can do this using the System.Data.SqlClient.SqlBulkCopy class.

Answer 2

I can’t say that I know why you get slower every time (it would seem that you do not actually clear “1000” each time, but actually add them or something like that), but if you want to dump data into in the database, you should use something like SqlBulkCopy , not the stored proc inside the for loop.

Answer 3

This is just an assumption, but you add more parameters at each iteration. Yes, they have the same names as before, but I don’t know if the SqlCommand class is enough to handle this or not. Try adding the parameters once, and then just set their value in a loop, i.e.

Out of cycle:

 command.Parameters.Add("@SignalID", SqlDbType.Int); 

Inside the loop:

 command.Parameters["@SignalID"].Value = Arg_Signal.SignalID; 

If this does not help you, you will need to profile the code and see where it is slow - it may not be the actual DB call.

As an aside, you can also try calling command.Prepare() when you run the same command many times - this will not fix this particular problem, but in any case it can slightly affect performance.