Método correcto para eliminar más de 2100 filas (por ID) con Dapper

dapper parameters sql-server

Pregunta

Intento utilizar Dapper para admitir mi acceso a datos para mi aplicación de servidor.

Mi aplicación de servidor tiene otra aplicación que coloca registros en mi base de datos a una velocidad de 400 por minuto.

Mi aplicación los saca en lotes, los procesa y luego los elimina de la base de datos.

Como los datos continúan fluyendo en la base de datos mientras estoy procesando, no tengo una buena manera de decir delete from myTable where allProcessed = true .

Sin embargo, sé el valor PK de las filas para eliminar. Así que quiero hacer una delete from myTable where Id in @listToDelete

El problema es que si mi servidor cae por 6 minutos, entonces tengo más de 2100 filas para eliminar.

Como Dapper toma mi @listToDelete y convierte cada uno en un parámetro, mi llamada para eliminar falla. (Causando que mi depuración de datos se retrase aún más).

¿Cuál es la mejor manera de lidiar con esto en Dapper?

NOTAS: he mirado Parámetros Tabled Valiosos pero por lo que puedo ver, no son muy performante . Esta pieza de mi arquitectura es el cuello de botella de mi sistema y necesito ser muy rápido.

Respuesta aceptada

Una opción es crear una tabla temporal en el servidor y luego utilizar la función de carga masiva para cargar todos los ID en esa tabla a la vez. A continuación, utilice una cláusula join, EXISTS o IN para eliminar solo los registros que cargó en su tabla temporal.

Las cargas masivas son una ruta bien optimizada en SQL Server y debería ser muy rápida.

Por ejemplo:

  1. Ejecute la instrucción CREATE TABLE #RowsToDelete(ID INT PRIMARY KEY)
  2. Use una carga masiva para insertar claves en #RowsToDelete
  3. Ejecute DELETE FROM myTable where Id IN (SELECT ID FROM #RowsToDelete)
  4. Ejecute DROP TABLE #RowsToDelte (la tabla también se eliminará automáticamente si cierra la sesión)

(Asumiendo Dapper) ejemplo de código:

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

Respuesta popular

Para que funcione este código, me fui del lado oscuro.

Desde Dapper hace mi lista en parámetros. Y SQL Server no puede manejar una gran cantidad de parámetros. (Nunca antes he necesitado parámetros de doble dígito). Tenía que ir con Dynamic SQL.

Así que aquí estaba mi solución:

string listOfIdsJoined = "("+String.Join(",", listOfIds.ToArray())+")";
connection.Execute("delete from myTable where Id in " + listOfIdsJoined);

Antes de que todos agarren sus antorchas y horquillas, déjenme explicar.

  • Este código se ejecuta en un servidor cuya única entrada es una fuente de datos de un sistema Mainframe.
  • La lista que estoy creando dinámicamente es una lista de longs / bigints.
  • Los largos / grandes son de una columna de Identidad.

Sé que construir SQL dinámico es malo, pero en este caso, no veo cómo puede llevar a un riesgo de seguridad.



Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow
Licencia bajo: CC-BY-SA with attribution
No afiliado con Stack Overflow