C#使用SQLBulkCopy或等效库有效地批量删除50000条记录 [英] C# Efficiently delete 50000 records in batches using SQLBulkCopy or equivalent library

问题描述

我正在使用该库来批量执行批量删除，如下所示:

I'm using this library to perform bulk delete in batches like following:

  while (castedEndedItems.Any())
  {
    var subList = castedEndedItems.Take(4000).ToList();
    DBRetry.Do(() => EFBatchOperation.For(ctx, ctx.SearchedUserItems).Where(r => subList.Any(a => a == r.ItemID)).Delete(), TimeSpan.FromSeconds(2));
    castedEndedItems.RemoveRange(0, subList.Count);
    Console.WriteLine("Completed a batch of ended items");
  }

您可以看到，我一次要删除4000批商品，然后将它们作为参数传递给查询...

As you can see guys I take a batch of 4000 items to delete at once and I pass them as argument to the query...

我正在使用此库执行批量删除:

I'm using this library to perform bulk delete:

https://github.com/MikaelEliasson/EntityFramework.Utilities

但是这样的性能是绝对糟糕的...我测试了几次应用程序，并删除了80000条记录，例如，这实际上需要40分钟！?

However the performance like this is absolutely terrible... I tested the application couple of times and to delete the 80000 records for example it takes literally 40 minutes!?

我应该注意，我用来删除(ItemID)的参数是varchar(400)类型，并且出于性能原因而对其进行了索引....

I should note that that parameter by which I'm deleting (ItemID) is of varchar(400) type and it's indexed for performance reasons....

还有其他我可以使用或调整此查询的库以使其运行更快的原因，因为当前的性能绝对糟糕..::

Is there any other library that I could possibly use or tweak this query to make it work faster, because currently the performance is absolutely terrible.. :/

推荐答案

如果您准备使用存储过程，则可以在没有任何外部库的情况下执行此操作:

If you are prepared to use a stored procedure then you can do this without any external library:

• 使用表值参数@ids
创建存储过程
• 为该表值参数定义SQL类型(假设是简单的PK，只是一个id列)

• 在存储过程中使用

• Create the sproc using a table valued parameter @ids
• Define a SQL type for that table valued parameter (just an id column assuming a simple PK)

• In the sproc use

delete from table where id in (select id from @ids);

在您的应用程序中创建一个DataTable并填充以匹配SQL表

In your application create a DataTable and populate to match the SQL table

此答案说明了该过程.

任何其他选择都需要做到这一点–或效率较低.

Any other option will need to do the equivalent of this – or something less efficient.

这篇关于C#使用SQLBulkCopy或等效库有效地批量删除50000条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！