Why insert statement is slow with SqlCommand?

Question

I am using SqlCommand to insert multiple records to the database but it takes long time to insert 2000 records, I did the following code:

using (SqlConnection sql = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand(query, sql))
    {
        sql.Open();
        int ff = 0;
        while (ff < 2000)
        {
            cmd.ExecuteNonQuery();//It takes 139 milliseconds approximately
            ff++;
            Console.WriteLine(ff);
        }

    }
}

But when I execute the following script in SSMS(Sql Server Management Studio) the 2000 records are stored in 15 seconds:

declare @i int;
set @i=1

while (@i)<=2000
begin
    set @i=@i+1
    INSERT INTO Fulls (ts,MOTOR,HMI,SET_WEIGHT) VALUES ('2018-07-04 02:56:57','0','0','0'); 
end

What's going on? Why is It so slow in executing the sentence?

Additional:

-The database is a SQL Database hosted in Microsoft Azure.

-The loading speed of my internet is 20 Mbits.

-The above query is not the real query, the real query contains 240 columns and 240 values.

-I tried to do a transaction following this example: https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx

-The sql variable is of type SqlConnection.

Thanks for your help.

Answer 1

It sounds like there is a high latency between your SQL server and your application server. When I do this locally, the pure TSQL version runs in 363ms, and the C# version with 2000 round trips takes 1061ms (so: about 0.53ms per round-trip). Note: I took the Console.WriteLine away, because I didn't want to measure how fast Console isn't!

For 2000 inserts, this is a pretty fair comparison. If you're seeing something massively different, then I suspect:

• your SQL server is horribly under-powered - it should not take 15s (from the question) to insert 2000 rows, under any circumstances (my 363ms timing is on my desktop PC, not a fast server)
• as already suggested; you have high latency

Note there are also things like "DTC" which might impact the performance based on the connection string and ambient transactions (TransactionScope), but I'm assuming those aren't factors here.

If you need to improve the performance here, the first thing to do would be to find out why it is so horribly bad - i.e. the raw server performance is terrible, and the latency is huge. Neither of those is a coding question: those are infrastructure questions.

If you can't fix those, then you can code around them. Table valued parameters or bulk-insert (SqlBulkCopy) both provide ways to transfer multiple rows without having to pay a round-trip per execute. You can also use "MARS" (multiple active results sets) and pipelined inserts, but that is quite an advanced topic (and most people tend to recommend not enabling MARS).

Answer 2

Make sure to minimize number of indexes on your table and use SqlBulkCopy as below

DataTable sourceData=new DataTable();
using (var sqlBulkCopy = new SqlBulkCopy(_connString))
{
    sqlBulkCopy .DestinationTableName = "DestinationTableName";
    sqlBulkCopy .WriteToServer(sourceData);
}