使用Dapper删除2100行(按ID)的正确方法 [英] Correct method of deleting over 2100 rows (by ID) with 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上载到该表中.然后使用join，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));

这篇关于使用Dapper删除2100行(按ID)的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！