Discussion :

[Badplayer1603]

Bonjour,

au hasard de mes lectures sur le net, je suis tombé sur cet article:
http://sqlblogcasts.com/blogs/tonyro...expansion.aspx

au vu des nombreux postes vantant les avantages des cte sur developpez.com, j'ai pris l'habitude d'écrire mes requêtes "complexes" avec des CTE et de reprendre les requêtes déjà existante en remplaçant les #t par des cte.

L'article du M. sus-cité, annonce qu'il ne faut pas les utiliser sauf pour la récursivité.

J'aimerais avoir vos avis sur le sujet.

Cdt.

[SQLpro]

Vieux débat et l'article est truffé de bêtises.

En effet, la table temporaire n'est pas comme certain le pense une table en mémoire, mais nécessite des opérations d'écriture de fichiers (journalisation) et de données (table instanciée).

En revanche la CTE n'oblige pas SQL Server à instancier la table sous-jacente à la CTE. C'est lui qui décidera au final, en fonction de la volumétrie, s'il y a lieu de tout faire en mémoire, de préparer une table temporaire, et de la remplir ou pas.
Ceci n'a d"ailleurs rien à voir avec la CTE proprement dit, qui n'est qu'une facilité d'écriture et que la requête finale soit écrite avec ou sans CTE, le résultat est le même en matière de plan d'exécution.

Mais les effets les plus néfastes de l'utilisation abusive des tables temporaires sont invisible dans la requête elle même :
1) l'ajout de données d'une nouvelle table, fusse t-elle temporaire, bouffe du cache au détriment de toutes les autres entrées du cache alors que ces données existe déjà dans les tables (redondance)...
2) les temps de traitements sont allongés du fait de la journalisation et de l'instanciation de la table temporaire, mais cela ne se voit pas car pas compté dans les métriques et de plus asynchrone.
Ce sont donc des ressources cache et disque volées au détriment des autres processus. C'est donc assez stupide !

Enfin dire qu'il faut réserver les CTE aux requêtes récursives est aussi une stupidité lapalissienne comme quelqu’un qui dirait, pour manger, avec une fourchette, il faut une fourchette... En effet, difficile de ce passer d'une CTE pour ce cas de figure vu que c'est le seul moyen !

Mais par expérience, je dirais en sus qu'en présence d'une requête récursive complexe et couteuse, il faut étudier le fait de procéder en plusieurs temps :

• Avant la récursivité, proposer une table temporaire
• Après la récursivité, proposer une table temporaire

Si ce découpage en 3, permet de diviser par 10 les temps de réponse, alors je prend. Si ça permet de gagner 30%, je laisse !

Récemment, c'est ce qui m'a valu de passer de 3 minutes à moins d'une seconde un traitement financier basé sur la profondeur des cotations jours par jours.
Mais toutes les tables temporaires étaient indexées après coup pour disposer de statistiques efficients qu'une requête récursive ne peut pas produire...

Enfin, l'article se base sur des données des tables systèmes dont les principaux inconvénients sont :
1) un faible volume inutilisable pour prouver quoi que ce soit en matière de temps de réponse (les temps de réponses n'étant pas linéaires, parce que l'optimiseur s’adapte à la volumétrie - changement de stratégie d'accès ou d’algorithme de traitement - ou bien encore activant le parallélisme
2) une distribution particulière des données des tables systèmes
3) une faible indexation des tables systèmes.
4) l'article est ancien et fait en 2008 sur la version 2008 au mieux. ENtre temps l'optimiseur à évolué en 2008 R2, 2012, 2014 et maintenant en 2016 !Donc tout à fait obsolète...
Bref du grand n'importe quoi...

Pour en savoir plus :



A +

[Badplayer1603]

Merci beaucoup pour votre retour,

votre réponse est claire et instructive.
Elle me rassure sur mon utilisation des CTE et m’éclaire sur la potentielle utilisation des tables temporaires.

Cdt

[SQLpro]

Merci beaucoup pour votre retour,

votre réponse est claire et instructive.
Elle me rassure sur mon utilisation des CTE et m’éclaire sur la potentielle utilisation des tables temporaires.

Cdt

En définitive, les tables temporaires, il ne faut pas en abuser !

A +

[elsuket]

Par ailleurs, il faut garder à l'esprit que pour une expression de table commune, il n'est pas possible pour l'optimiseur d'estimer le nombre de lignes qui sera retourné par chaque boucle. L'expression de table commune est donc efficace pour des cardinalités relativement stables durant la récursivité, mais pas toujours quand la distribution des valeurs sur le prédicat de jointure est assez variable.

Donc comme pour toute requête, seule une revue du plan d'exécution associée à la sortie des options de session SET STATISTICS IO, TIME ON permet de vérifier que le plan est au moins assez bon et qu'il a de bonnes chances d'être stable dans le temps. Pour rejoindre l'exposé de SQLPro, l'utilisation de tables temporaires relève souvent d'une mauvaise modélisation de la base de données à gérer, ou d'une connaissance incomplète des possibilités offertes par T-SQL.

@++