samedi 6 avril 2013

Challenge SQL : Upgrade du DELETE


Etant donné que je suis en panne d'inspiration pour faire de billets intéressants et qu'en plus je n'en fais déjà pas souvent, je vais me lancer dans une petite suite de problématique que je vais vous adresser, à vous nombreux lecteurs de ce blog (nombreux relativement à mon cercle de connaissances professionnelles, autant dire que mis à part mes collègues, personne ne passe ici).




Alors voilà le contexte (plutôt simple pour une première). Nous avons une table de plusieurs centaines de millions de lignes, typiquement une table de fait (dans la grande majorité des cas). Sur cette table, nous souhaitons réaliser une suppression des lignes en fonction du périmètre que nous souhaitons réalimenter : de l’annule et remplace en bonne et due forme. Là, on constate que le lot tourne pendant de longues minutes pour réaliser une opération qui nous semble basique (je précise que c’est un serveur de bourrin derrière). En ajoutant un peu de log (on évite le mode debug de SSIS sur les tables à forte volumétrie car ralentit encore plus l’exécution de vos lots), nous constatons que c’est la phase de DELETE des lignes qui prend du temps. Une suppression de 80 millions de ligne prend presque 3 minutes : c’est beaucoup trop. L’instruction de suppression est la suivante :

DELETE FROM MaTable
WHERE DateKey BETWEEN 20060101 AND 20061231

Nous décidons d’intervenir et de mettre en place un élément de base de données qui nous permettra de réduire fortement ce temps de suppression des lignes. Le temps nécessaire à la mise en place de cette amélioration doit être court et elle doit demander un minimum de gestion après son implémentation. Une fois cette dernière réalisée, nous avons, pour la même instruction, un temps de 45 secondes.

Quelle est cette solution miracle? La première personne à répondre dans les commentaires se verra offrir une bière au prochain AfterWork de GUSS qui aura lieu le 17 avril à 19h au Charly-Birdy, 1 place Etienne Pernet, Paris 15ème, Métro Commerce.

------------------------------------------------------------------------------------------------------------

Voici la réponse tant attendue. Avant toute chose, merci pour votre participation et bien entendu, tout le monde pouvait jouer. Rappellons lescontraintes : ne pas toucher à la requête de suppression mais faire en sorte, via la mise en place de “quelques chose”, que la suppression des lignes prennent moins de temps. Il fallait également que la mise en place de ce “quelques chose” soit rapide et ne demande pas trop de maintenance à notreDBA préféré.Vu dans les commentaires, le partitionnement et le switch associé offrent effectivement un gain de performance conséquent : c’est même l’idéal. Seulement, il faut modifier la requête initiale et la gestion des partitions demande un travail relativement important par rapport à la solution proposée. De plus attention à la synthaxe suivante qui supprime toutes les lignes de la table :
DELETE FROM MaTable
FROM (SELECT * FROM MaTable
WHERE DateKey BETWEEN 20060101 AND 20061231) as Derivee

Prenons maintenant un exemple plus abordable en terme de tests. Soit une table de fait avec 31 millions de lignes. Voici les résultats observés avec les configurations suivantes en corrélation avec vos propositions dans les commentaires (les temps sont en secondes) :
Configuration
NonClustered (DateKey)
Clustered (DateKey)
Compression (Page)
Config 1



Config 2
X


Config 3

X

Config 4

X
X



Configuration
Elapsed time
Logical reads
CPU time
Config 1
41
9467351
41
Config 2
100
36142241
152
Config 3
22
323742
16
Config 4
77
283512
70
















Pourquoi un tel écart entre la heap table, le non clusterd index et le clusterd index compressé? Le non clustered index nécessite de supprimer les lignes dans la tables et les entrées dans l’index. Lorsqu’une table est compressée par page, SQL Server met en place quelques objets supplémentaires pour gérer cette compression et notamment un page dictionnary qui a grosso modo le même mode d’organisation que ceui d’Xvelocity (appliqué au column store index et à Tabular). Passé un certain seuil de nombre de ligne à supprimer/mettre à jour (je ne connais pas le mode de calcul de ce seuil interne au moteur, si quelqu’un peut nous renseigner, nous sommes preneur), SQL Server doit recréer ce dictionnaire de données.

Au final, un clustered index sans compression constitue la solution répondant à notre problématique.
Tout cela pour mettre en évidence le fait de ne pas oublier que les clusterd index ne servent pas qu’aux SELECT : ce sont des éléments indispensables de toutes vos tables (les heap tables sont très rarement avantageauses). Attention, cet exemple ne tend pas à démontrer qu’il faut orienter la création de vos clustered index uniquement en fonction des DELETE, mais ces opérations sont à prendre en considération quand leur nombre devient important et quand elles gèrent de grands volumes de données.

Les références :
http://technet.microsoft.com/en-us/library/cc917672.aspx#EDAA
http://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/01/18/details-on-page-compression-page-dictionary.aspx

7 commentaires:

  1. moi moi moi !!
    j'peux jouer ?

    RépondreSupprimer
  2. a défaut d'utiliser le Switch de Partition, il est peut être possible d'utiliser une table dérivé

    DELETE FROM MaTable
    FROM (SELECT * FROM MaTable WHERE DateKey BETWEEN 20060101 AND 20061231) as Derivee

    RépondreSupprimer
  3. Ce commentaire a été supprimé par l'auteur.

    RépondreSupprimer
    Réponses
    1. Je dirais que le plus simple c'est la compression + indexe sur DateKey

      Supprimer
  4. En effet, je vote aussi pour un index sur la DateKey ...

    RépondreSupprimer
  5. Bien joué, et en effet ma requête supprime tout le contenu de la table (bad work)
    il faudrait faire :
    DELETE FROM MaTable
    FROM MaTable F
    JOIN (SELECT PK FROM MaTable WHERE DateKey BETWEEN 20060101 AND 20061231) as FD ON F.PK= FD.PK -- PK est la ou les colonne(s) composite du Clustered Index

    et réaliser un index non clustered ... ( DateKey ) Include (PK)


    l'inconvénient c'est que cela produit un double scan, mais soulage le fait de remplacer le précédent CLUSTER INDEX par un nouveau (puisque celui-ci est unique par table).

    En tous cas merci pour ton article je LIKE +1 etc ...

    RépondreSupprimer
  6. Salut Patrice,
    Très bon article qui montre bien l'utilité de créer des indexes clustered sur nos tables de faits.
    Au passage je suis impressionné des temps d'exécution (c'est sur du FastRack ?)
    Je serai curieux de voir les performances obtenues avec du partition switching du genre :
    - une partition par année
    - un switch de partition de la table de fait vers la table de staging pour l'année 2006 (instantanée)
    - un truncate de la table de staging (instantané)
    - suppression des indexes sur la table de staging (instantané)
    - bulk insert des données dans la table de staging (tps d'insertion optimum puisque pour l'instant c'est une heap)
    - création de l'index clustered sur la table de staging et mise en place de la compression sur la partition cible (c'est là que ça risque de prendre un peu de temps)
    - et switch de partition de la table de staging vers la table de fait (instantané)
    Cela éviterait de créer de la fragmentation et permettrait de garder la compression par page qui réduit pas mal les I/O sur les select.
    Certes la mise en œuvre est plus longue qu'une simple création d'index clustered (quoique la création d'un index clustered sur une bonne grosse table peux prendre pas mal de temps :-)).
    En tout cas sur mon pauvre portable, j’étais à 20% de tps d’exécution en plus avec la compression cf. http://wp.me/p2yhHc-8N

    RépondreSupprimer