[2005] OPTION (RECOMPILE) m'a sauvé la mise

**Sergejack** · 08/06/2011, 12h25

Bonjour.

Comme j'ai rencontré un problème plutôt obscure, j'aimerais partager avec vous la solution que je lui ai trouvé au cas où un jour vous rencontriez le même sans ne plus avoir quoi faire.

J'avais un problème un performance grave.
Pour une raison qui m'échappe peut-être encore un query impliquant une table variable au sein d'une transaction était infiniment lent.

J'étais pourtant sûr d'avoir intelligemment écrit la définition de cette table variable (avec une clé primaire nécessaire pour une bonne performance) et de mes query mais le fait était là : mon query n'en finissait jamais.

J'ai fait plusieurs essaies d'écriture alternative de mes query, rien n'y changeait.
À la lecture du query plan je pouvais quand même remarqué une particularité, l'index du à la clé primaire de ma table n'était pas utilisé et l'estimation du coût des query était euphorique en comparaison avec le résultat réel.

J'ai tenté quelque chose d'inhabituel : remplacer la table variable par une table temporaire.
Et là, non seulement le query pouvait s'achever mais en plus il s'achevait rapidement (comme je prévoyais qu'il le fasse avec une table variable).

J'en ai tiré la conclusion que Sql server fesait peut-être le plan d'excecution impliquant la table variable sans considérer correctement que cette table évoluait très fort pendant la tansaction et avant d'atteindre les query "à problème".

J'ai donc rajouté l'option RECOMPILE à mon query et réutiliser la table variable plutôt qu'une table temporaire.
Mais à nouveau, les performances étaient atroces.

En fait, dans la solution que j'ai maintenant et qui me satisfait, le query est toujours celui là. Il m'a juste fallu chercher pour découvrir qu'il me fallait augmenter la taille de TEMPDB.

Mais sans l'option RECOMPILE, quoi que soit fait à TEMPDB, mon query avec table variable est catastrophique.

J'espère que ça en aidera certains et, à moins que d'autres utilisateurs du forum m'ouvre les yeux sur d'autres éléments qui m'auraient échappés, je copierai ce post dans la rubrique des contributions.

**elsuket** · 08/06/2011, 12h55

Bonjour,

La raison est pourtant simple : SQL Server ne maintient strictement aucune statistique sur une variable de type TABLE.
Il estime par défaut qu'une telle "table" ne contient qu'une ligne, ce qui l'amène à effectuer de fausses estimations de cardinalités.

En revanche sur une table temporaire, les statistiques sont recalculées toutes les 6 modifications (sympa pour le CPU ...).
Donc SQL Server est capable d'estimer correctement les cardinalités, et donc votre requête s'exécute proprement.

Dans tous les cas, l'utilisation de variables de type TABLE ou de tables temporaire est une pure duplication de données dans TempDB.
C'est une base de données système qui est utilisée pour tout un tas d'autres choses comme les jointures ou les tris, et je suis sûr que vous le savez.
Cela montre très souvent une mauvaise conception de la base de données utilisateur.

Plus de détails ici

@++

**Sergejack** · 08/06/2011, 13h11

Envoyé par elsuket

Bonjour,

La raison est pourtant simple : SQL Server ne maintient strictement aucune statistique sur une variable de type TABLE.
Il estime par défaut qu'une telle "table" ne contient qu'une ligne, ce qui l'amène à effectuer de fausses estimations de cardinalités.

Je me disais bien...

Envoyé par elsuket

En revanche sur une table temporaire, les statistiques sont recalculées toutes les 6 modifications (sympa pour le CPU ...).
Donc SQL Server est capable d'estimer correctement les cardinalités, et donc votre requête s'exécute proprement.

Dans tous les cas, l'utilisation de variables de type TABLE ou de tables temporaire est une pure duplication de données dans TempDB.
C'est une base de données système qui est utilisée pour tout un tas d'autres choses comme les jointures ou les tris, et je suis sûr que vous le savez.
Cela montre très souvent une mauvaise conception de la base de données utilisateur.

Plus de détails ici

@++

J'ai récemment beaucoup relu sur le sujet.
Par contre une chose continue de m'étonner, mon query va un rien plus vite avec des tables temporaires.
C'est très curieux était donné que pour les tables temporaires il est supposé y avoir un surcout du fait de logs plus complets.

**elsuket** · 08/06/2011, 13h18

Une mauvaise estimation de cardinalité surpasse clairement une mise à jour de statistiques sur une table dont le nombre de lignes est faible.

Avez-vous une idée plus ou moins exacte du nombre de lignes que contient votre table temporaire ?

Pour vous donner une idée, un mise à jour de statistiques sur toutes les tables et vues indexées d'un client dont la base de données fait 3To prend 2h30...
Mais tout dépend des tables et du nombre de colonnes des statistiques : celles des index ou celles que l'utilisateur peut créer peuvent être sur plusieurs colonnes.
Celles qui sont crées automatiquement (donc dans votre cas) le sont sur une colonne, sauf si votre clé primaire est composite.

@++

**zinzineti** · 08/06/2011, 13h42

Personnellement, je pense qu'en dehors des UDFs il vaut mieux utiliser les tables temporaires standard ...
C'est ce que j'essayais de mettre en évidence ici

A+

**Sergejack** · 08/06/2011, 13h53

Envoyé par zinzineti

Personnellement, je pense qu'en dehors des UDFs il vaut mieux utiliser les tables temporaires standard ...
C'est ce que j'essayais de mettre en évidence ici

A+

Aucune motivation générale n'apparait dans votre article, seulement des considérations pour des cas particulier.
Si vous pensez qu'il vaut mieux toujours employer des tables temporaires plutôt que des tables variables hors des UDF et que vous pouvez le démontrez, n'hésitez pas à compléter votre article.

**zinzineti** · 08/06/2011, 14h47

Envoyé par Sergejack

Aucune motivation générale n'apparait dans votre article, seulement des considérations pour des cas particulier.
Si vous pensez qu'il vaut mieux toujours employer des tables temporaires plutôt que des tables variables hors des UDF et que vous pouvez le démontrez, n'hésitez pas à compléter votre article.

Ce qui est général dans le petit billet :

1. Impossible de faire un ROLLBACK dans une transaction avec une variable table

2.Impossible de créer un index Non-Cluster sur une variable table

3.Impossible de faire un ALTER sur une variable table

4.Impossible de faire directement du SQL dynamique avec une variable table

5. La déclaration de la variable table doit être dans le même lot d'instruction que la requête sinon il y a erreur. Vérifions cela avec la commande "interpreteur" GO (qui n'est pas une commande SQL). la commande "interpreteur" GO force l'envoi d'instruction SQL au serveur

6. La variable table est très utile pour les UDFs retournant une table

Il n'y a rien de particulier pour ces points....

Et pour chaque point vous avez un exemple. Si vous avez le temps relisez l'article

Mais dans tous les cas il n'y a rien de plus instructif que l'expérience...vous venez d'en faire et je pense que vous avez tirez votre votre propre conclusion sur ce sujet

A+

**elsuket** · 08/06/2011, 14h58

Pour moi c'est ni l'un, ni l'autre.

D'autre part les UDF scalaires ou qui retournent une table qui n'est pas "en ligne" sont bien connues pour être assez contre-performantes

@++

**Sergejack** · 08/06/2011, 17h31

Envoyé par zinzineti

1. Impossible de faire un ROLLBACK dans une transaction avec une variable table

Jusqu'à présent, je n'ai jamais considéré faire le rollback sur une table à usage temporaire (pour la bonne raison que ça ne m'aurait été d'aucun intérêt).

Envoyé par zinzineti

2.Impossible de créer un index Non-Cluster sur une variable table

En voilà une remarque UNIQUE.

Envoyé par zinzineti

3.Impossible de faire un ALTER sur une variable table

Heu... non. Il suffit de modifier la définition de la table à même le query.
Vachement plus simple que DROP/CREATE ou d'un ALTER sur un table (global) temporaire.

Envoyé par zinzineti

4.Impossible de faire directement du SQL dynamique avec une variable table

Très vrai, mais je continue de penser que cette remarque aussi tient des cas (très) particulier d'utilisation.

Envoyé par zinzineti

5. [B]La déclaration de la variable table doit être dans le même lot d'instruction que la requête sinon il y a erreur.

Tant mieux. Ça donnera à tous l'occasion de faire la différence entre un batch et une série de querys.

Envoyé par zinzineti

6. La variable table est très utile pour les UDFs retournant une table

Très vrai, mais je continue de penser que cette remarque aussi tient des cas (très) particulier d'utilisation.

Envoyé par zinzineti

Il n'y a rien de particulier pour ces points....

Dans quel sens du terme ?
Y a des déclarations fallacieuse, si c'est ce que vous voulez dire.

Envoyé par zinzineti

Et pour chaque point vous avez un exemple. Si vous avez le temps relisez l'article

Moi pas avoir tout compris, moi pas trop intelligence.

**zinzineti** · 08/06/2011, 21h07

Ah une relecture point par point nous permet de nous comprendre et d'avancer ...

Envoyé par Sergejack

Dans quel sens du terme ?
Y a des déclarations fallacieuse, si c'est ce que vous voulez dire.

Là je me suis mal exprimé peut être. Au fait je parle des 6 points que j'ai évoqué précédemment. Qui ne sont pas des cas particuliers.

Envoyé par Sergejack

Moi pas avoir tout compris, moi pas trop intelligence.

Là c'est pas gentil ça. Je le prends positivement... (comme de l'humour)

Allons dans la même direction :

Zi Toi Pas Compri , Moi EZpliqué ANcore

Salut Bonne soirée

**Sergejack** · 09/06/2011, 09h06

Attardez vous quand même sur ma motivation à mettre le mot "unique" en capital.

Bonne journée.

**mikedavem** · 09/06/2011, 13h55

Difficile de généraliser l'utilisation de l'une ou l'autre méthode. En fonction du contexte et des spécifitiés liées à chacune d'elle on pourra choisir d'utiliser les tables temporaires ou les variables de tables.

Sur des transactions à forte volumétrie j'utiliserai sans aucun doute les tables temporaires plutôt que les variables de tables pour les raisons cités par Elsuket. On peut également crééer et utiliser les index pour gérer le tout.

Avec des petits objets j'aurai tendance à utiliser les variables de tables (a condition de ne pas en avoir besoin en dehors du contexte d'une procédure évidemment).

Je pense qu'avant tout il ne faut pas oublier que les variables de tables comme les tables temporaires ne résident pas totalement en mémoire. Beaucoup ont tendance à penser que les variables de tables ne sont traités uniquement qu'en mémoire.

++

**Sergejack** · 16/06/2011, 11h20

Il est possible d'avoir des indexes sur des tables variables (j'ai d'ailleurs plus que répété un indice).
Par contre il n'est pas possible d'y faire référence par des HINTS (puisque ces derniers ne sont pas permis à l'emploi d'une table variable).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
DECLARE @Example TABLE (
a  INT
, b  INT
, c  INT UNIQUE -- Hop, un index
)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
DECLARE @Example TABLE (
 a INT
, b INT
, c  INT
, UNIQUE(c, b) -- Hop, un autre index
)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
DECLARE @Example TABLE (
 a INT
, b INT
, c  INT
, d INT IDENTITY -- PRIMARY KEY : utile mais non nécessaire
, UNIQUE(c, b, d) -- Hop, un index garanti sur 100% de scénarios
)