Inutile de vous rappeler que les contraintes sont indispensables pour préserver l’intégrité des données. Mais celles-ci jouent un autre rôle indispensable pour l’optimiseur de requêtes. En effet pour pouvoir générer un plan d’exécution, celui-ci prend en compte ces contraintes. Le fait qu’une contrainte soit désactivée ou « non fiable » fait qu’elle n’est plus prise en compte par l’optimiseur et par conséquent peut influencer sur les performances. Nous verrons dans ce billet quelques cas concrets.

Pour trouver les contraintes ou clés étrangères qui ne sont plus prises en compte par l’optimiseur, je vous renvoie sur le billet de Elsuket.

–> 1er cas : contraintes CHECK

Prenons une table hébergeant des données de transactions de stock, une table d’articles et une table de magasin de stock. Ces tables sont simplifiés au maximum pour une meilleure compréhension. Voici le script de création de ces tables :

La table dbo.detail_transaction_stocks possède deux clés étrangères qui référencent les tables dbo.article et dbo.magasin. Elle possède également une contrainte de vérification sur la colonne code_tran. Cette contrainte restreint le domaine des valeurs que peut avoir la colonne code_tran. Jusqu’ici rien d’exceptionnel .

Evidemment l’application qui gère cette base de données ne permet pas de faire d’erreur lors de la sélection des données de transaction de stock en fonction d’un code de transaction. Celles-ci sont disponibles par le biais de combo box ou peuvent être également vérifiées lors de la validation d’un écran. Cependant, le service informatique a développé une petite application sous Excel qui permet d’interroger et d’analyser les données en fonction des codes de transactions. Cette application est destinée principalement aux responsables de stock et à certaines directions comme la finance par exemple. Dans cette nouvelle application, le code des transactions est entré manuellement, ce qui implique, par conséquent, qu’il peut exister des erreurs de saisie.

Une requête de sélection peut être de la forme :

Notez que l’utilisateur a commis une faute de frappe concernant le code des transactions (RECP ou lieu de RCP). Regardons le plan d’exécution de cette requête :

On n’aurait pu s’attendre à un balayage de l’ensemble des tables concernées par la requête mais il n’en est rien. L’optimiseur sait, en se basant sur la contrainte de vérification CHK_CODE_TRAN, qu’il n’existe aucune ligne de données possédant une valeur de code de transaction égale à ‘RECP’. Il remplace donc l’ensemble des tables par l’opérateur Constant Scan. Cet opérateur ne fait aucun accès au moteur de stockage de SQL Server et ne retourne aucune ligne de données. Ceci permet d’exécuter cette requête très rapidement, en prenant un minimum de mémoire et en ne générant aucun verrou sur l’ensemble des objets concernés par notre requête.

Désactivons maintenant la contrainte de vérification CHK_CODE_TRAN. (Cela peut arriver pour améliorer les opérations de chargement massif de données par exemple) et réactivons là:

Regardons ensuite le plan d’exécution de notre requête de sélection :

Celui-ci a changé. Maintenant l’ensemble des tables concernées par la requête sont prises en compte par l’optimiseur bien qu’il n’existe aucune ligne de données correspondante au prédicat de la requête. Pourtant nous avons bien réactiver la contrainte de vérification. Alors que s’est il passé ? Pour cela nous allons interroger la vue système sys.check_constraints.

On remarque que la contrainte CHK_code_tran n’est plus considérée comme fiable par SQL Server (colonne is_not_trusted à 1). Dans ce cas là l’optimiseur ne tient pas compte de cette contrainte lorsque le plan d’exécution est créé. Ce comportement est tout à fait normal. Faisons une brève analogie avec une trieuse automatique de pièces. Celle-ci trie automatiquement dans les compartiments adéquates les pièces que vous lui fournissez en entrée. Vous êtes donc certain qu’une pièce sera rangée automatiquement dans le bon compartiment. Désactivez la fonction de tri pendant un moment et continuez à insérer des pièces. Enfin réactivez à nouveau la fonction de tri et celle-ci trie de nouveau correctement les pièces que vous continuez à lui fournir. Maintenant si l’on vous demande si toutes les pièces sont correctement triées dans leur compartiment êtes vous en mesure d’affirmer que c’est le cas ? La réponse est évidement non puisque pendant un moment la fonction de tri n’était pas activée. C’est la même chose pour SQL Server. Rien ne garantit que pendant le moment où la contrainte était désactivée que les nouvelles valeurs de la colonne code_tran soient correctes et correspondent au domaine des valeurs imposée par cette contrainte. Lorsque cela arrive SQL Server initialise la colonne is_not_trusted de la vue système sys.check_constraint à 1. Maintenant comment être certain que l’ensemble des pièces soient correctement triées ? Vous n’avez pas le choix, il faut vérifier à un à un les compartiments pour le savoir. Pour SQL Server c’est encore la même chose. Il faut réactiver la contrainte de vérification avec l’option WITH CHECK qui permet de vérifier la cohérence de l’ensemble des valeurs de la colonne code_tran.

 

La contrainte est à nouveau fiable :

 

. et peut être réutilisée dans le plan d’exécution lié à notre requête :

 

–> 2ème cas : Clés étrangères

Pour ce deuxième cas, nous allons créer une vue qui réunit l’ensemble des données concernant les transactions de stock et répond aux attentes des différents responsables de service. Pour cela nous devons récupérer les données des transactions de stocks, les désignations d’articles et les noms des magasins cibles.

Prenons pour exemple une requête réalisée par le service des finances qui permet d’extraire les mouvements de certains articles sans restriction de dates. Le service des finances, pour ses besoins, n’a besoin que des codes article, codes de transaction et les quantités correspondantes.

Le plan d’exécution de cette requête est le suivant :

On remarque qu’il n’y a qu’un seul opérateur de balayage de table. Celui-ci concerne la table dbo.detail_transaction_stocks. Un opérateur scalaire est également présent et permet, entre autre, de calculer le résultat de la colonne mvt. Les contraintes de clés étrangères obligent par nature que pour une ligne de données de la table des transactions il existe au préalable une ligne de données dans les autres tables. Notre requête ne récupère que les données de la table dbo.detail_transaction_stocks (colonnes code_tran et code_art). Par conséquent il n’est pas nécessaire d’interroger les deux autres tables. Cette optimisation permet donc d’économiser un certain nombres de lectures de pages non nécessaires (et donc d’I/O).

Supposons maintenant que nous devons procéder à une insertion en masse de données dans la table des transactions de stocks. Nous désactivons les contraintes de clés étrangères sur cette table pour des raisons de performance :

Une fois notre insertion de données effectuée nous réactivons les contraintes de clés étrangères de la manière suivante :

Réexécutions la requête vu précédemment. Le plan d’exécution est maintenant le suivant :

Celui-ci a changé du tout au tout. Nous avons deux opérateurs supplémentaires de parcours ou de balayage de table qui concernent les trois tables liées par nos opérations d’équi-jointures (dbo.detail_transaction_stocks, dbo.article et dbo.magasin). Nous avons donc un plan d’exécution beaucoup moins optimisé que le précédent avant la désactivation et la réactivation des contraintes de clés étrangères. Pour les mêmes raisons évoquées avec les contraintes de vérification CHECK, SQL Server ne peut plus utiliser les contraintes de clés étrangères lors de la génération des plans d’exécutions. Rien ne garantit qu’à présent qu’il existe pour une ligne de données dans la table des transactions de stock une ligne correspondante dans les autres tables. SQL Server est donc obligé d’interroger les tables dbo.article et dbo.magasin pour réaliser les opérations de jointure et éliminer les lignes n’ayant pas de correspondance.

Comme tout à l’heure nous voyons que les contraintes de clés étrangères ne sont plus considérés comme fiable par l’optimiseur de requêtes (vue système sys.foreign_keys):

Les valeurs de la colonne is_not_trusted nous le confirme.

Comme pour les contraintes de vérification, il faut utiliser l’option WITH CHECK qui permettra une vérification préliminaire des valeurs de la clé étrangère avant sa réactivation et une prise en compte à nouveau par l’optimiseur de requêtes. Je vous laisse vérifier .

 

–> 3ème cas : Vues partitionnées

Les contraintes de vérification sont importantes dans le cas des vues partitionnées en terme de performance. En effet si celles-ci sont désactivées ou ne sont pas fiables SQL Server ne saura plus avec certitude où se situeront précisément les données et devra interroger l’ensemble des tables sous jacentes à la vue. Pour plus d’explications, je vous invite à lire un de mes précédents billets sur ce sujet.

Bonne vérification de contraintes !!

David BARBARIN (Mikedavem)
Elève ingénieur CNAM Lyon