271
8.3 Optimisation du code SQL
Notez que si vous ne séparez pas les deux tests par un GO, vous verrez la table dans
les deux SELECT, le DECLARE étant placé en premier par la compilation du code
SQL.
Comme pour une table temporaire, la table contenue dans la variable réside en
mémoire jusqu’au moment où elle atteint une certaine taille. Elle est ensuite écrite
dans tempdb. Le gain apporté par les variables de type table est donc limité. Y a-t-il
alors une différence entre les deux options ? Oui, la variable de type table est plus
rapide principalement parce qu’elle consomme moins de ressources pour la
maintenir : il n’y a pas de calcul de statistiques de colonnes sur une variable de type
table par exemple, le verrouillage des lignes est moindre, et ne dure que le temps de
l’instruction de modification, de même que la transaction de modification, même si
elle est journalisée (il suffit pour le constater d’utiliser la fonction fn_dblog() que
nous avons déjà utilisée), n’est pas enrôlée dans une transaction explicite, elle s’exécute et est validée automatiquement dans sa propre transaction « privée ». Ces
avantages peuvent aussi se révéler des inconvénients, selon l’utilisation qu’on veut
faire de la variable de type table. L’absence de calcul de statistiques sur les colonnes
peut être contre-productive sur des variables de type table volumineuses. Comme
l’optimiseur n’a aucune statistique, son plan d’exécution va se baser sur l’estimation
du retour de zéro ou d’une seule ligne, quelle que soit la réalité. On peut le vérifier
simplement, par exemple en regardant le plan d’exécution estimé de cette requête
(qui retourne en réalité 1 763 lignes sur ma version de la base resources) :
DECLARE @t TABLE (Name SYSNAME)
INSERT @t SELECT name FROM sys.system_objects
SELECT * FROM @t
Figure 8.14 — Estimation requête table variable
Le plan d’exécution généré peut donc se révéler bien moins performant
qu’avec une table temporaire.
Il est de même impossible de créer des index sur une variable (à part un index lié
à une contrainte : déclaration de clé primaire ou de clé unique, possibles unique-
8.3 Optimisation du code SQL
Notez que si vous ne séparez pas les deux tests par un GO, vous verrez la table dans
les deux SELECT, le DECLARE étant placé en premier par la compilation du code
SQL.
Comme pour une table temporaire, la table contenue dans la variable réside en
mémoire jusqu’au moment où elle atteint une certaine taille. Elle est ensuite écrite
dans tempdb. Le gain apporté par les variables de type table est donc limité. Y a-t-il
alors une différence entre les deux options ? Oui, la variable de type table est plus
rapide principalement parce qu’elle consomme moins de ressources pour la
maintenir : il n’y a pas de calcul de statistiques de colonnes sur une variable de type
table par exemple, le verrouillage des lignes est moindre, et ne dure que le temps de
l’instruction de modification, de même que la transaction de modification, même si
elle est journalisée (il suffit pour le constater d’utiliser la fonction fn_dblog() que
nous avons déjà utilisée), n’est pas enrôlée dans une transaction explicite, elle s’exécute et est validée automatiquement dans sa propre transaction « privée ». Ces
avantages peuvent aussi se révéler des inconvénients, selon l’utilisation qu’on veut
faire de la variable de type table. L’absence de calcul de statistiques sur les colonnes
peut être contre-productive sur des variables de type table volumineuses. Comme
l’optimiseur n’a aucune statistique, son plan d’exécution va se baser sur l’estimation
du retour de zéro ou d’une seule ligne, quelle que soit la réalité. On peut le vérifier
simplement, par exemple en regardant le plan d’exécution estimé de cette requête
(qui retourne en réalité 1 763 lignes sur ma version de la base resources) :
DECLARE @t TABLE (Name SYSNAME)
INSERT @t SELECT name FROM sys.system_objects
SELECT * FROM @t
Figure 8.14 — Estimation requête table variable
Le plan d’exécution généré peut donc se révéler bien moins performant
qu’avec une table temporaire.
Il est de même impossible de créer des index sur une variable (à part un index lié
à une contrainte : déclaration de clé primaire ou de clé unique, possibles unique-