How to quick insert data to sql server? When I use while cicle Its begin fast but after 5000 lines its' going to be very slowly

Question

When I try to write 100,000 lines to a database, I encounter very slow processing in the data cycle. I used lazy queries earlier and used the "AsNoTracking()" method, but it did not help. What's more, who can explain this phenomenon, when the loop has just started, when it goes through the loop pretty quickly, but as soon as it exceeds 5000 lines, the addition to the parameter is almost a second for each iteration?

`while (enumerator.MoveNext())
{
    MonitorlControlTable dt = enumerator.Current as MonitorlControlTable;
    var user = users.Where(x => x.Name == dt.UserNameTb).FirstOrDefault();
    var action = actions.Where(x => x.Name == dt.ActionTb.Value.ToString()).FirstOrDefault();
    var project = projects.Where(x => x.Name == dt.ProjectNameTb).FirstOrDefault();
    var transaction = transactions.Where(x => x.Name == dt.TransactioNameTb).FirstOrDefault();
    TransactionBoard transactBoard = dbContext.TransactionBoard.Add(new TransactionBoard
{
    User = user != null ? user : new EF.User { Name = dt.UserNameTb },
    Action = action != null ? action : new EF.Action { Name = dt.ActionTb.Value.ToString() },
    Project = project != null ? project : new Project { Name = dt.ProjectNameTb, Path = dt.ProjectNameTb },
  Transaction = transaction != null ? transaction : new Transaction { Name = dt.TransactioNameTb },
DateTime = dt.WriteDateTimeTb.Value
  });
dbContext.MonitorControl.Add(new MonitorControl
  {
    ElementId = dt.ElementIdTb.Value,
    Category = category.Where(x => x.Id == dt.CategoryIdTb).FirstOrDefault(),
    TransactionBoard = transactBoard
  });
  });
}`

What I did wrong?

See following : https://stackoverflow.com/questions/15767803/entity-framework-query-slow-but-same-sql-in-sqlquery-is-fast — jdweng, Aug 30 '19 at 14:06

F.K · Accepted Answer · 2019-10-23T14:16:29.443

I used Bulk Insert as EF was too slow for inserting 14K row insert.

here is an example of bulk insert:
string BulkSaveConnection = //connection;
using (var connection = new SqlConnection(BulkSaveConnection))
{
    SqlTransaction transaction = null;
    connection.Open();
    try
    {
        transaction = connection.BeginTransaction();
        using (var sqlBulkCopy = new SqlBulkCopy(connection, sqlBulkCopyOptions.TableLock, transaction))
        {
            sqlBulkCopy.DestinationTableName = "[ServerName].[TableName]";
            sqlBulkCopy.ColumnMappings.Add("sourceColumnName1", "DestinationColumnName");
            sqlBulkCopy.ColumnMappings.Add("sourceColumnName2", "DestinationColumnName");
            //
            //
            //
            sqlBulkCopy.WriteToServer(dataTable);
       }
       transaction.Commit();
   }
    catch (Exception ex)
    {
       Console.WriteLine(ex.ToString());
       transaction.Rollback();
    }

}

OK. But I have constrained table wich generete new Id which I use another table. How to do it by INSERT BULK& — Vladimir, Aug 30 '19 at 14:04
Do you have an id column that its auto generating? it will be very similar to ADO connection — F.K, Aug 30 '19 at 14:15

score 0 · Answer 2 · answered Aug 30 '19 at 14:40

you can save your transactions in a list:

var list = new List<TransactionBoard>();

while() {
...
  list.Add(newTransactionBoard);

  if (list.Count == 5000) {    
    await context.BulkInsertAsync(list);
    list.Clear();
  }
}
await context.BulkInsertAsync(list);

How to quick insert data to sql server? When I use while cicle Its begin fast but after 5000 lines its' going to be very slowly

2 Answers2