Discussion :

[Kropernic]

Et à la demande de mikedavem (qui efface mes messages le bougre :p), voici le plan et les messages (voir pièce jointe).

[mikedavem]

Oui oui dsl fausse manipulation

Tu pourrais éventuellement essayer de créer un index cluster sur la table temporaire #T2 pour voir. Comme cela à vue de nez tu devrais encore voir certaines opérations de l'optimiseur disparaitre.

++

[Kropernic]

Oui oui dsl fausse manipulation

Pas de souci ^^

Tu pourrais éventuellement essayer de créer un index cluster sur la table temporaire #T2 pour voir. Comme cela à vue de nez tu devrais encore voir certaines opérations de l'optimiseur disparaitre.

++

Pour l'index cluster, il me faudrait une colonne unique qui n'est pas présente en l'état.
Ajouter une colonne avec la fonction ROW_NUMBER() ?

Mais pour le moment, ça a l'air de bien tourner avec l'index proposer par dbaffaleuf. Le jour où ça se gâte, je me souviendrai de tenter l'index cluster.

[mikedavem]

Tu n'as pas besoin d'avoir de colonne unique pour un index cluster. Je n'ai pas dit clé primaire mais index cluster :-)

++

[Kropernic]

Tu n'as pas besoin d'avoir de colonne unique pour un index cluster. Je n'ai pas dit clé primaire mais index cluster :-)

++

Ah ? Je croyais qu'un index cluster était toujours unique.

Où aurais-je été pêché ça ?

[mikedavem]

Ah ? Je croyais qu'un index cluster était toujours unique.

Où aurais-je été pêché ça ?

Peut-être du fait que lorsqu'on implémente une clé primaire par défaut SQL Server créée un index unique cluster ... :-)

++

[Kropernic]

Probable oui.

Je testerai à l'occasion.

[dbaffaleuf]

Je serais, à l'heure actuelle, bien incapable de reproduire la démarche que vous avez eue pour arriver à cette solution.

Les dernières stats io envoyées:

Code :

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

1
2
 
Table '#T2__ ... ___000000000014'. Scan count 1, logical reads 1564563, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

1564563 logical reads sur #T2, lecture complète de la table dans le plan (table scan). CRD_SERIAL est colonne principale de l'index pour résoudre le prédicat #T2.CRD_SERIAL < #T3.SER_ID, et les colonnes additionnelles (TCA_ID, CRD_RECHARGEABLE) pour couvrir le select et la jointure. Mais il faudrait voir le dernier plan pour confirmer.

[Kropernic]

Mais il faudrait voir le dernier plan pour confirmer.

Le dernier plan se trouve en pièce jointe du premier message de cette page-ci.

[Kropernic]

Une dernière question :

Puis-je tirer comme conclusion qu'il vaut mieux toujours utiliser des tables temporaires plutôt que des CTE ?

[dbaffaleuf]

Une dernière question :

Puis-je tirer comme conclusion qu'il vaut mieux toujours utiliser des tables temporaires plutôt que des CTE ?

Il n'y a pas de toujours ou de jamais (sauf auto_shrink), il n'y a que des 'ça dépend'

En l'occurence dans ton cas, ça dépend du manque de précision dans les estimations de cardinalités (le comptage des lignes en sortie de chaque opérateur) qui induit le mauvais choix de jointure (nested loops join). Je me demande si à l'origine tes indexes ne sont pas fragmentés, et donc quelle est la dernière date de calcul des stats sur ces indexes.

[Kropernic]

Il n'y a pas de toujours ou de jamais (sauf auto_shrink), il n'y a que des 'ça dépend'

En l'occurence dans ton cas, ça dépend du manque de précision dans les estimations de cardinalités (le comptage des lignes en sortie de chaque opérateur) qui induit le mauvais choix de jointure (nested loops join). Je me demande si à l'origine tes indexes ne sont pas fragmentés, et donc quelle est la dernière date de calcul des stats sur ces indexes.

Même réponse que précédemment :

Je ne connais encore rien de toute ce qui touche aux statistiques. Je sais que ça existe, quelque part, mais ça s'arrête là.

J'attends que sqlpro pondent un article sur le sujet

[SQLpro]

Une dernière question :

Puis-je tirer comme conclusion qu'il vaut mieux toujours utiliser des tables temporaires plutôt que des CTE ?

Oh que non !!!!

Lisez mon livre sur SQL et dans la partie administration des serveurs, dont le chapitre est en ligne, je traite des problèmes de performances....
Or les tables temporaires pose des problèmes de performances difficiles à cerner.
En effet, comme il faut bien stocker en RAM les données de ces tables temporaires, cela met de la pression mémoire, qui fait dégager d'autres données. Ce n'est donc pas la table temporaire qui sera moins efficace... C'est TOUT LE RESTE !
En sus, une table temporaire est journalisée comme toute table de toute base, ce qui suppose des opérations d'écritures de fichiers...
Enfin toute requête, CTE ou non, peut conduire à créer des tables temporaire pour réaliser le traitement (woktable).
On ne peut donc pas opposer ces deux choses !

A +

[Kropernic]

En effet, comme il faut bien stocker en RAM les données de ces tables temporaires, cela met de la pression mémoire, qui fait dégager d'autres données. Ce n'est donc pas la table temporaire qui sera moins efficace... C'est TOUT LE RESTE !

La partie en gras me fait un peu peur... Ok ma requête a été boostée et réponds au quart de tour mais si c'est pour que le reste du serveur rame à cause de ça quand ce sera en production (je dramatise sûrement mais bon), je ne suis pas sûr que cela en vaille vraiment la peine.

Du coup, ne serait-il pas préférable que je cherche un autre moyen d'exécuter cette requête qui soit tout autant performant ?

Je me souviens d'un papier que vous avez écrit où vous parlez de l'efficacité des vues indexées dans le cas de calculs importants.

Je ne fais pas vraiment de calcul mais pas mal de fonction d'agrégation MIN et MAX. Est-ce que une ou plusieurs vues indexées ne serait pas une meilleure solution ?

[StringBuilder]

S'il y a des min/max, peut-être aussi des colonnes calculées en mode persistantes ?

[Kropernic]

Nope, pas de colonnes calculées persistantes. Du moins, pas dans mes "vraies" tables.

Après, est-ce que les colonnes produites par les fonction min et max dans les #tables sont considérées comme telle, je ne suis pas assez calé pour le savoir.

[mikedavem]

La partie en gras me fait un peu peur... Ok ma requête a été boostée et réponds au quart de tour mais si c'est pour que le reste du serveur rame à cause de ça quand ce sera en production (je dramatise sûrement mais bon), je ne suis pas sûr que cela en vaille vraiment la peine.

Il faut prendre avec parcimonie (dans le sens où il faut appliquer à votre contexte) ce qui est dit et tester ta nouvelle implémentation de requête et voir l'impact de celle-ci par rapport à ta première version (CTE) sur ton serveur avec les données réelles.

++

[Kropernic]

Ah mais c'est pas pour tout de suite ça...

La DB n'est même pas encore sur le serveur de production (logique, c'est un projet en développement).

En plus, on attend de nouveaux serveurs d'ici la fin de l'année ^^

EDIT :

J'ai tenté l'expérience avec les vues mais je n'arrive même pas à un résultat qui tourne... Je ne savais pas que les vues étaient si contraignantes dès qu'on commence à utiliser des vues dans la définition d'une nouvelle vue.

[Kropernic]

Je reviens sur le sujet...

D'après le message de sqlpro, l'emploi de tables temporaires (et dans certains cas, celui de CTE's qui conduit à la création de tables temporaires) peut poser des problèmes de performances aux autres traitements du SGDBR.

Du coup, que faudrait-il alors mettre en œuvre comme solution ?

J'imagine que la "simple" requête reste la piste à privilégier plutôt que de multiples CTE's ou tables temporaires non ?

Cette "simple" requête existe mais elle prend énormément de temps (j'ai déjà patienté 30 min avant de l'annuler). Le problème vient probablement d'un problème d'indexation de mes tables.

Voici la requête en question :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
     SELECT
             CRD.TCA_ID,
             CRD.CRD_RECHARGEABLE,
             MIN(CRD.CRD_SERIAL) AS 'MIN',
             MAX(CRD.CRD_SERIAL) AS 'MAX'
     FROM
             DBO.T_GIFT_COMMANDE_GFC GFC
                 INNER JOIN dbo.T_CARD_CRD CRD
                     ON    GFC.GFT_ID = CRD.GFT_ID
                  OUTER APPLY    (
                             SELECT
                                      MIN(SER_ID) AS 'CRD_SERIAL'
                             FROM
                                     DBO.T_SERIAL_SER SER
                             WHERE
                                     SER_ID > CRD.CRD_SERIAL
                                  AND SER_ID NOT IN    (
                                                     SELECT
                                                             CRD_SERIAL
                                                     FROM
                                                             DBO.T_GIFT_COMMANDE_GFC GFC2
                                                                 INNER JOIN dbo.T_CARD_CRD CRD3
                                                                     ON    GFC2.GFT_ID = CRD3.GFT_ID
                                                      WHERE
                                                             CRD3.TCA_ID = CRD.TCA_ID
                                                          AND GFC2.CMD_ID = @CMD_ID
                                                     )
                             ) T
     WHERE
             GFC.CMD_ID = @CMD_ID
      GROUP BY
             CRD.TCA_ID,
             CRD.CRD_RECHARGEABLE,
             T.CRD_SERIAL
     ORDER BY
             CRD.TCA_ID,
             CRD.CRD_RECHARGEABLE,
             [MIN]

Cette requête est une adaptation d'une requête que iberserk avait proposé pour un problème similaire. J'avais choisi de la réécrire (car je ne maitrise pas les FULL/OUTER APPLY) avec des CTE's que j'utilise régulièrement.

Selon vous connaissances expertes, que faut-il préférer ? La requête unique où la décomposition en plusieurs requêtes ?

EDIT :

Ajout du plan de requête estimé en pièce jointe.

[Kropernic]

Une piste aussi qui n'est pas à exclure, serait de renvoyer les données brutes à l'applicatif client et d'y faire le traitement.

Niveau traitement, cela devrait aller très vite. Par contre, le transfert des données via le réseau, ça risque de merdé...

Donc de base, je serais plutôt pour faire le taff côté DB même si c'est un traitement grandement cosmétique...