Calling sqlCommand in a loop increasing exec time every step

Question

Hi All,

I've got a loop that executes the stored procedure in a loop with over 40,000 iterations, like so:

 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 times I get are 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 1250ms"

[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 total 101093.75ms 1515.625ms"

Does anyone have an idea why these execution times are increasing? I need to run this code with over a million iterations - by the rate its going its going to take a minute to execute one iteration...

Many thanks

Answer 1

I can't say I know why you it goes slower each time (it would sound like you are not actually clearing the "1000" each time, but actually appending them or something) but if you are looking to dump data into a database you should be using something like SqlBulkCopy, not a stored proc inside a for loop.

Answer 2

This is just a guess, but you're adding more parameters on each iteration. Yes, they have the same names as before, but I don't know if the SqlCommand class is clever enough to handle that or not. Try adding the parameters once and then just setting their value in the loop, ie.

Outside of the loop:

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

Inside the loop:

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

If that doesn't help you'd have to profile the code and see where it's slow - it may not be the actual DB call.

As an aside, you could also try calling command.Prepare() when executing the same command many times - it wouldn't fix this particular problem, but may just make a slight performance difference for the better anyway.

Answer 3

Is there any special logic in your stored proc or are you just inserting into a table.

If there is no special logic or you can do that logic in .NET then have a look at performing a Bulk Insert. You can do this by utilising the System.Data.SqlClient.SqlBulkCopy class.