3

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.

Improve this question
14
  • One big aspect is that every insert happens in its own transaction, and for reach SQL Server has to verify it's written to disk before acknowledging this to the client. Wrap those 2000 inserts in one single transaction and you should see a different number. Commented Jul 5, 2018 at 14:46
  • three questions: what is sql? what is the latency between your sql server and the application? and how long is "long time", compared to the 15s for the TSQL version. I'll be honest, for 2000 rows even in the TSQL version, 15s is a huge time - is that server particularly slow? Commented Jul 5, 2018 at 14:46
  • @JeroenMostert while that is true, it doesn't really explain the difference between the two versions, as the TSQL version would also be 2000 transactions Commented Jul 5, 2018 at 14:47
  • I suspect there is some important environment difference other than C# vs SSMS. for one thing, you're writing a console line each time in C# but not in SSMS. Commented Jul 5, 2018 at 14:48
  • Should also add, the query you show in SSMS is really not the way to do this. There should be one insert, not 2000. It should all run in under one second, not 15. Commented Jul 5, 2018 at 14:52

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Hi @Marc, thanks for answering, I am using a SQL Server hosted in Microsoft Azure and the loading speed of the internet is 20 Mbits; the query could be big because actually columns are not 4, columns are 240. Would this be the problem?
"high latency" is not related to your Mbits. If you want to eliminate latency, construct one large query or batch, rather than 'woodpecker' the server, which will have fresh latency per hit.
@AndresCamacho the 20 Mbits is bandwidth, not latency; two very different numbers with very different meanings. A UPS truck full of hard disks has incredible bandwidth, and terrible latency. What is the latency like?
@AndresCamacho that said: yes, cloud hosts are notorious for high latency, especially if you're connecting to it from outside the same farm - are your app-server and sql-server in the same location? If not: simply the speed of light is a real issue here, and you cannot go faster than the speed of light.
@MarcGravell Currently the app-server is my laptop, and I don't know how to measure the latency with the server
|
1

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);
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.