Speed up insert - Insert list at once

Question

How can i speed up time while inserting multiple data?

I get data from excel and I store them in List. In my loop, I open connection for every row and then I close with my Insert function. Insert function insert data row by row. Sometimes this operation takes a lot of time. My insert loop like this

private void btnSave_Click(object sender, RoutedEventArgs e)
{
    if (cmpList.Count == 0)
    {
        MessageBoxHelper.ShowError("List can not be inserted");
        return;
    }

    foreach (var item in cmpList)
    {
        item.StartDate = dpStart.SelectedDate.Value;
        item.FinishDate = dpFinish.SelectedDate.Value;
        item.Insert();
    }

    MessageBoxHelper.ShowInformation("List was inserted successfully!");
    comboCampaignType.IsEnabled = true;
}

I create cmpList for all row from excel like this

for (int i=0; i<excelRowCount; i++)
{
    Campaign cmp = new Campaign();
    cmp.Type = 1;
    cmp.PriceP.Amount = double.Parse((string)sheet.GetRow(row).GetCell(1).Value, CultureInfo.InvariantCulture);
    cmp.PriceV.Amount = double.Parse((string)sheet.GetRow(row).GetCell(1).Value, CultureInfo.InvariantCulture);
    cmp.PriceP.Currency = (string)sheet.GetRow(row).GetCell(2).Value;
    cmp.PriceV.Currency = (string)sheet.GetRow(row).GetCell(2).Value;
    cmp.MinOrder = Convert.ToDouble((string)sheet.GetRow(row).GetCell(3).Value);
    cmpList.Add(cmp);
}

So, is there any way to insert list at once? Any ideas how to speed up this operation ?

What's a ```cmpList```? Once we find out, we should be able to recommend an appropriate action. E.g. turning the collection into a concurrent one and inserting concurrently from many different tasks. Depending on what ```cmpList``` is, it may not even be necessary to turn it into a concurrent collection. — Piotr Justyna, Jul 13 '17 at 12:18
Possible duplicate of [Inserting multiple rows in mysql](https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql) — JonathanDavidArndt, Jul 13 '17 at 12:22
It is not duplicate. I am not using mySql code at c# side. I am using stored procedure @JonathanDavidArndt — Kemal Güler, Jul 13 '17 at 12:53
No problem. If it's not a duplicate, it's not a duplicate. On the surface, the other question had some of the same considerations that yours did, but was in a different tech domain. Just wanted to make sure you saw it. — JonathanDavidArndt, Jul 13 '17 at 13:16
`INSERT INTO tbl (a,b) VALUES (1,2), (11,22), (5,4), ...;` -- this is an efficient "bulk insert". — Rick James, Jul 13 '17 at 22:25

score 3 · Answer 1 · answered Jul 13 '17 at 21:25

There are different reasons that may cause performance issues. You are using Stored Procedure which will be faster than using sql statement in c# side. However; creating connections for each row will be costly too. You should consider retaining a single connection and execute insertion (keep in mind to avoid connection timeout). If you are able to ignore the stored procedure, you should follow the batch insert options such as SqlBulkCopy or you can even build custom SQL insert statements like 1000 items per insert statement.

Since you state that "Sometimes this operation takes a lot of time", the problem may be the database server load, constaints, triggers etc. You should avoid using excessive triggers and constaints which may cause huge performance issues. You can check it by turning of the constraints temporarily as mention in this post.

Thanks for advice! I will look for SqlBulkCopy. – Kemal Güler Jul 14 '17 at 06:17 — Kemal Güler, Jul 14 '17 at 06:17

Speed up insert - Insert list at once

1 Answers1