使用 Dapper 删除超过 2100 行(按 ID)的正确方法

2021-12-29 00:00:00 parameters sql-server dapper

我正在尝试使用 Dapper 支持我的服务器数据访问应用程序.

I am trying to use Dapper support my data access for my server app.

我的服务器应用程序有另一个应用程序，它以每分钟 400 条的速度将记录放入我的数据库中.

My server app has another application that drops records into my database at a rate of 400 per minute.

我的应用将它们分批拉出来，处理它们，然后从数据库中删除它们.

My app pulls them out in batches, processes them, and then deletes them from the database.

由于在我处理过程中数据会继续流入数据库，因此我没有什么好方法可以说delete from myTable where allProcessed = true.

Since data continues to flow into the database while I am processing, I don't have a good way to say delete from myTable where allProcessed = true.

但是，我知道要删除的行的 PK 值.所以我想做一个 delete from myTable where Id in @listToDelete

However, I do know the PK value of the rows to delete. So I want to do a delete from myTable where Id in @listToDelete

问题是，如果我的服务器宕机甚至 6 分钟，那么我有超过 2100 行要删除.

Problem is that if my server goes down for even 6 mintues, then I have over 2100 rows to delete.

由于 Dapper 接受了我的 @listToDelete 并将每一个都变成了一个参数，所以我对 delete 的调用失败了.(导致我的数据清除进一步落后.)

Since Dapper takes my @listToDelete and turns each one into a parameter, my call to delete fails. (Causing my data purging to get even further behind.)

在 Dapper 中处理此问题的最佳方法是什么?

What is the best way to deal with this in Dapper?

注意:我已经查看了表值参数，但从我所见，它们不是很性能.我的这一架构是我系统的瓶颈，我需要非常非常快速.

NOTES: I have looked at Tabled Valued Parameters but from what I can see, they are not very performant. This piece of my architecture is the bottle neck of my system and I need to be very very fast.

推荐答案

一种选择是在服务器上创建一个临时表，然后使用批量加载工具一次性将所有 ID 上传到该表中.然后使用连接、EXISTS 或 IN 子句仅删除您上传到临时表中的记录.

One option is to create a temp table on the server and then use the bulk load facility to upload all the IDs into that table at once. Then use a join, EXISTS or IN clause to delete only the records that you uploaded into your temp table.

批量加载是 SQL Server 中优化良好的路径，速度应该非常快.

Bulk loads are a well-optimized path in SQL Server and it should be very fast.

例如:

  1. 执行语句CREATE TABLE #RowsToDelete(ID INT PRIMARY KEY)
  2. 使用批量加载将键插入#RowsToDelete
  3. 执行 DELETE FROM myTable where Id IN (SELECT ID FROM #RowsToDelete)
  4. 执行DROP TABLE #RowsToDelte(如果关闭会话，表也会自动删除)
  1. Execute the statement CREATE TABLE #RowsToDelete(ID INT PRIMARY KEY)
  2. Use a bulk load to insert keys into #RowsToDelete
  3. Execute DELETE FROM myTable where Id IN (SELECT ID FROM #RowsToDelete)
  4. Execute DROP TABLE #RowsToDelte (the table will also be automatically dropped if you close the session)

(假设 Dapper)代码示例:

(Assuming Dapper) code example:

conn.Open();

var columnName = "ID";

conn.Execute(string.Format("CREATE TABLE #{0}s({0} INT PRIMARY KEY)", columnName));

using (var bulkCopy = new SqlBulkCopy(conn))
{
    bulkCopy.BatchSize = ids.Count;
    bulkCopy.DestinationTableName = string.Format("#{0}s", columnName);

    var table = new DataTable();                    
    table.Columns.Add(columnName, typeof (int));
    bulkCopy.ColumnMappings.Add(columnName, columnName);

    foreach (var id in ids)
    {
        table.Rows.Add(id);
    }

    bulkCopy.WriteToServer(table);
}

//or do other things with your table instead of deleting here
conn.Execute(string.Format(@"DELETE FROM myTable where Id IN 
                                   (SELECT {0} FROM #{0}s", columnName));

conn.Execute(string.Format("DROP TABLE #{0}s", columnName));

相关文章