Méthode correcte de suppression de plus de 2100 lignes (par identifiant) avec Dapper

dapper parameters sql-server

Question

J'essaie d'utiliser le support Dapper mon accès aux données pour mon application serveur.

Mon application serveur a une autre application qui supprime les enregistrements dans ma base de données à un taux de 400 par minute.

Mon application les extrait par lots, les traite et les supprime de la base de données.

Comme les données continuent à circuler dans la base de données pendant le traitement, je n'ai pas de moyen de dire à delete from myTable where allProcessed = true .

Cependant, je connais la valeur PK des lignes à supprimer. Donc, je veux faire une delete from myTable where Id in @listToDelete

Le problème est que si mon serveur tombe en panne pour même 6 minutes, alors j'ai plus de 2100 lignes à supprimer.

Comme Dapper prend mon @listToDelete et transforme chacun en paramètre, mon appel à la suppression échoue. (Causer ma purge des données pour aller encore plus loin.)

Quelle est la meilleure façon de gérer cela dans Dapper?

NOTES: J'ai regardé les Paramètres Valorisés par Tabled mais d'après ce que je peux voir, ils ne sont pas très performants . Ce morceau de mon architecture est le goulot d'étranglement de mon système et je dois être très très rapide.

Réponse acceptée

Une option consiste à créer une table temporaire sur le serveur, puis à utiliser la fonction de chargement en bloc pour télécharger tous les ID dans cette table à la fois. Utilisez ensuite une clause join, EXISTS ou IN pour supprimer uniquement les enregistrements que vous avez téléchargés dans votre table temporaire.

Les charges en bloc sont un chemin bien optimisé dans SQL Server et devraient être très rapides.

Par exemple:

  1. Exécutez l'instruction CREATE TABLE #RowsToDelete(ID INT PRIMARY KEY)
  2. Utilisez un chargement en bloc pour insérer des clés dans #RowsToDelete
  3. Exécutez DELETE FROM myTable where Id IN (SELECT ID FROM #RowsToDelete)
  4. Exécutez DROP TABLE #RowsToDelte (la table sera également automatiquement supprimée si vous fermez la session)

(Assumant Dapper) exemple de code:

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

Réponse populaire

Pour faire fonctionner ce code, je suis allé du côté obscur.

Depuis Dapper fait ma liste dans les paramètres. Et SQL Server ne peut pas gérer beaucoup de paramètres. (Je n'ai jamais eu besoin de paramètres à deux chiffres auparavant). Je devais aller avec Dynamic SQL.

Alors voici ma solution:

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

Avant que tout le monde attrape leurs torches et leurs fourches, laissez-moi vous expliquer.

  • Ce code s'exécute sur un serveur dont la seule entrée est un flux de données provenant d'un système Mainframe.
  • La liste que je crée dynamiquement est une liste de longs / bigints.
  • Les longs / bigints proviennent d'une colonne Identité.

Je sais que construire du SQL dynamique est un mauvais juju, mais dans ce cas, je ne vois pas comment cela conduit à un risque de sécurité.




Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi