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.

Andres Camacho
  • 367
  • 4
  • 17
  • 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. – Jeroen Mostert Jul 05 '18 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? – Marc Gravell Jul 05 '18 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 – Marc Gravell Jul 05 '18 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. – user1443098 Jul 05 '18 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. – user1443098 Jul 05 '18 at 14:52
  • Personally I would rethink this a bit. Instead of doing 2,000 individual inserts I would use a stored procedure that receives a table valued parameter. Then you can do this whole thing as a single statement that inserts as many rows as you pass it. – Sean Lange Jul 05 '18 at 14:52
  • Testing locally: the pure TSQL version takes 363ms, and via the C# using the same `INSERT` line as you: 1061ms - which isn't bad for 2000 round trips! – Marc Gravell Jul 05 '18 at 14:56
  • FWIW, this insert runs in under a second: `INSERT INTO FULLS (ts,MOTOR,HMI,SET_WEIGHT) SELECT TOP (2000) '2018-07-04 02:56:57','0','0','0' FROM sys.columns, sys.columns _` – user1443098 Jul 05 '18 at 14:56
  • @SeanLange you don't need a stored proc to use table variables - you just need to define the custom data types, but... – Marc Gravell Jul 05 '18 at 14:57
  • Please don't tell me you are attempting to insert 2000 rows that are exactly the same to your table. If you need all records as a transaction (so if one insert fails all others will be rolled back), I would recommend using a table valued parameter to make it a single insert statement instead of 2000 different insert statement. Note that each time you do `cmd.ExecuteNonQuery();` it's a round-trip between the c# and SQL Server . – Zohar Peled Jul 05 '18 at 15:05
  • @MarcGravell true that you don't need to use a stored proc but always a good idea to get some layer separation instead of having everything all tightly coupled together. ;) – Sean Lange Jul 05 '18 at 15:07
  • @SeanLange there are advantages and disadvantages to both :) – Marc Gravell Jul 05 '18 at 15:12
  • @MarcGravell are you saying there are advantages to having a tightly coupled app with no layers? Or discussing procedures? Very different animals, of course both are well outside of the current discussion. :) – Sean Lange Jul 05 '18 at 15:14
  • @SeanLange I was still talking about "stored procs vs (parameterized) ad-hoc SQL commands"; but... yeah, we're very far off-topic :) – Marc Gravell Jul 05 '18 at 15:15

2 Answers2

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

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 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? – Andres Camacho Jul 05 '18 at 15:23
  • "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. – Davesoft Jul 05 '18 at 15:31
  • @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? – Marc Gravell Jul 05 '18 at 15:39
  • @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. – Marc Gravell Jul 05 '18 at 15:42
  • @MarcGravell Currently the app-server is my laptop, and I don't know how to measure the latency with the server – Andres Camacho Jul 05 '18 at 15:50
  • @AndresCamacho well `ping` would be a crude measure, although that doesn't measure a lot; perhaps the best way would be to issue 500 `select 1;` dummy queries, and time how long it takes, then divide by 500 – Marc Gravell Jul 05 '18 at 16:12
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);
}
rameshsriram
  • 29
  • 4