none
Suppression massive sous SqlServer 2008 RRS feed

  • Question

  • Bonjour,

    Je viens vers vous car j'ai un problème technique.
    J'ai une saisie qui envoie une liste de guid à ma méthode qui doit les supprimer. Il peut y avoir plusieurs milliers de guid dans cette liste. (List<Guid> idFactures)

    Le soucis, c'est que si je fais un

    DELETE Facture FROM Facture WHERE IdFacture IN (idFactures[1], idFactures[2], ..., idFactures[xx])
    j'obtiens une chaine d'instruction qui dépasse la taille autorisée par SQL. A titre indicatif, cette limite est obtenu avec environ 30 000 guid dans le IN.

     

     

    Auriez vous une solution qui me permette de faire ceci autrement qu'avec un IN?

     

    Merci de vos réponses!

    David.

    mercredi 7 avril 2010 13:08

Réponses

  • Bonjour,

    Je rejoins l'idée de changer de process afin d'effectuer la suppression.
    Le plus simple et rapide serait de créer une table contenant uniquement les ID des factures à supprimer.
    Puis d'effectuer la suppression via INNER JOIN sur cette même table.

    DELETE F FROM FACTURE F
    INNER JOIN FACTURE_A_SUPPRIMER FAS
    ON F.ID = FAS.ID

    Pour l'insertion des id dans la table FACTURE_A_SUPPRIMER, le C# n'est pas très performant, je vous conseil de créer un fichier via C# puis d'appeler une procédure stockée effectuant un bulk insert de ce fichier.

    ( La meilleur solution serait de créer le fichier puis d'utiliser un lot SSIS afin de gérer l'insert mais à defaut du lot SSIS le bulk insert est une bonne alternative. )

    Cordialement,

    Julien.

    • Marqué comme réponse daweed60 mercredi 14 avril 2010 11:27
    mercredi 14 avril 2010 10:12

Toutes les réponses

  • Bonsoir,

    Pourquoi ne pas procéder par lots de GUID plus petits par exemple .. mais en plusieurs fois. Par exemple des lots de 5000 GUID à la fois ...

    Cela vous permettra 2 choses :

    - Eviter les locks escalations ... et les blocages d'accès intempestifs à votre table pendant les opérations de suppression

    - la limite de chaîne de caractères dans votre IN ...

    ++


    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    mercredi 7 avril 2010 20:45
    Modérateur
  • Bonjour,

    Merci de votre réponse! Mais nous y avions pensé et j'avais prototypé ce code ci :

    if (idFactures.Count > 30000)
    {
        while (idFactures.Count > 0)
        {
            int range = 30000;
    
            if (idFactures.Count < 30000)
                range = idFactures.Count;
    
            List<Guid> idFacturesTemp = idFactures.GetRange(0, range);
            idFactures.RemoveRange(0, range);
    
            command.CommandText = SqlQueryService.CreateGuidListParameter(queryDelete, "@idFactures", idFacturesTemp);
            command.ExecuteNonQuery();
        }
    }

    Le soucis c'est au niveau des performances ici. Il m'a fallu 23 secondes pour faire la suppression de 65534 factures à partir de leur Guid. Je pense que ce n'est pas tant SQL qui galère mais le traitement C# cette fois-ci... Déjà que 4 secondes paraissent énormes pour ma hierarchie!

    jeudi 8 avril 2010 07:05
  • Je pense également qu'il y a de fortes chances que c'est votre code soit en cause. J'ai eu le même cas pour un job d'archivage .... Nous avons transposé le code CSharp en du code purement SQL dans une procédure stockée et les performances étaient au rendez vous ..

    ++


    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    jeudi 8 avril 2010 16:32
    Modérateur
  • Bonjour,

     

    Désolé de ma réponse tardive, je n'avais pas vu que vous aviez répondu.

    Nous avions pensé aux procédures stockées mais le soucis c'est toujours la longueur limitée de l'instruction SQL. En code purement SQL je pourrais faire un traitement par lot équivalent à celui en C#?


    Merci d'avance!

    lundi 12 avril 2010 08:30
  • Bonjour,

     

    Par « purement SQL » vous comprenez sans utiliser la liste C# ?

     

    Cordialement,

    Alex


    Appel à contribution ! http://social.msdn.microsoft.com/Forums/fr-FR/vbasicfr/thread/ff4910bf-dca4-4664-b01e-b58bd860a643
    mardi 13 avril 2010 10:45
  • Bonjour,

    Il n'y a pas de solution purement SQL. Fournir un très grand nombre de Guid (ou tout autre type de paramètre) en une seule passe n'est pas possible.

    Je chercherais plutôt une solution à la source, comment est généré une aussi grande liste ? Il est peut-être possible de réaliser la (ou les) requête SQL qui génère cette liste à partir de critères. Il est probable que ces critères sont moins nombreux que la liste finale. La solution optimale serait alors de réaliser toute la logique de cette (ou de ces) requête avec la suppression des factures dans une procédure stockée.

    Cordialement


    Jean-Michel Guemguem
    Tekigo
    http://blog.tekigo.com
    mardi 13 avril 2010 11:47
  • @Alex : non non. J'aurais plutot vu une manière de "découper" cette longue liste en SQL directement au lieu du C#.

     

    @Jean-Michel : on ne peut pas réduire cette liste à quelques critères malheureusement. Ici j'ai parlé de factures mais ce n'est pas du tout l'objet de mon application. C'était juste plus simple plutot que de vous parler d'agriculture!

     

    Je suis en train de tester une piste avec des CTE multiples. Je vous tiendrais évidemment au courant de l'avancée de mes recherches.

     

    En tout cas merci de vous pencher sur mon problème!

    mardi 13 avril 2010 12:20
  • Je rejoins Jean-Michel sur ce point.

    Si la liste des GUID est conséquente, il faut peut être abandonné l'idée de la passer en paramètre à une procédure stockée. Il faut peut être revoir la logique de suppression. Comment est générée cette liste ? Est ce que je peux directement l'incorporer dans la procédure etc ...

    ++


    MCDBA | MCITP SQL Server 2005 / SQL Server 2008 | LPI Linux 1
    mardi 13 avril 2010 12:20
    Modérateur
  • Bonjour,

    Je rejoins l'idée de changer de process afin d'effectuer la suppression.
    Le plus simple et rapide serait de créer une table contenant uniquement les ID des factures à supprimer.
    Puis d'effectuer la suppression via INNER JOIN sur cette même table.

    DELETE F FROM FACTURE F
    INNER JOIN FACTURE_A_SUPPRIMER FAS
    ON F.ID = FAS.ID

    Pour l'insertion des id dans la table FACTURE_A_SUPPRIMER, le C# n'est pas très performant, je vous conseil de créer un fichier via C# puis d'appeler une procédure stockée effectuant un bulk insert de ce fichier.

    ( La meilleur solution serait de créer le fichier puis d'utiliser un lot SSIS afin de gérer l'insert mais à defaut du lot SSIS le bulk insert est une bonne alternative. )

    Cordialement,

    Julien.

    • Marqué comme réponse daweed60 mercredi 14 avril 2010 11:27
    mercredi 14 avril 2010 10:12
  • Bonjour,

     

    @Julien_at_NP6 : c'est la solution que nous sommes en train de tester. En ce qui concerne l'insertion dans la table nous utilisons en fait une variable de type table mais dans l'idée c'est ça. Un INNER JOIN se comporte mieux qu'un DELETE IN.

    mercredi 14 avril 2010 11:27
  • Bonjour,

    La variable de type table est une bonne solution, tout en mémoire et peu d'accès au fichier de log => meilleure performance :)

    Son seul default est qu'elle n'offre pas autant de possibilitée qu'une table physique (pas de truncate, pas de redefinition de structure...) mais dans votre cas cela n'est pas nécessaire donc vous avez tout à gagner à l'utiliser.

    L'INNER JOIN est magique dès que l'on souhaite s'occuper de données en masse et cela pour tout SELECT, UPDATE, DELETE ...

    Cordialement,

    Julien

     

     

    jeudi 15 avril 2010 09:36