sql server - Correct method of deleting over 2100 rows (by ID) with Dapper

Question

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

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.

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.

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

Problem is that if my server goes down for even 6 mintues, then I have over 2100 rows to 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.)

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.

Answer 1

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.

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

For example:

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)

(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));