Discussion :

[Kropernic]

Bonjour,

Une petite question sur les index qui doit être triviale pour certains.

Soit une table T avec les colonnes A, B, C, D et E (peu importe les types je pense).

Sur cette table, un index est créé sur les colonnes C et D.

L'index sera-t-il utilisé si une requête est faite sur cette table avec un filtre sur la colonne C dans une jointure et un filtre sur la colonne D dans la clause WHERE ?

[SQLpro]

Oui, très probablement au moins en SCAN, mais le mieux serait sans doute (D, C) que (C, D);

A +

[Kropernic]

Je veux bien vous croire sur parole mais j'aimerais comprendre pourquoi ?

Vous n'auriez pas écrit un article là-dessus par hasard ? ^^
(je sais qu'il y en a un sur les index mais pas de cas comme celui que je décris)

Je cherche de la théorie. J'ai toujours du mal avec les index...

[dbaffaleuf]

Pas nécessairement.

Si l'index ne couvre pas toutes les colonnes de la table, l'optimiseur peut choisir un clustered index scan pour récupérer les colonnes additionnelles plutôt qu'un index seek + key lookup. Il n'y a pas de règle reproductible à l'infini. Ça dépend de beaucoup trop de critères, si le prédicat est discriminant, les autres indexes, les stats, etc...

Meuilleures lectures:
- Craig Freedman (http://blogs.msdn.com/b/craigfr/)
- Paul White (http://sqlblog.com/blogs/paul_white/)

[Kropernic]

Si je vous suis, suivant le nombre de colonnes se trouvant dans la clause SELECT, il peut être plus intéressant de lire directement toutes les lignes de la table pour directement tout avoir plutôt que de faire la recherche dans un index pour ensuite aller chercher dans la table les valeurs manquantes (via l'index cluster toujours si j'ai bien compris).

Si c'est juste, niveau théorie, je crois que je suis au point. Niveau pratique, c'est autre chose...

Un exemple concret...
J'ai cette procédure stockée:

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
USE [GIFT_MANAGEMENT]
GO
 
/****** Object:  StoredProcedure [dbo].[UP_COMMANDE_GET_DETAIL_CRD]    Script Date: 11/20/2012 08:49:18 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[UP_COMMANDE_GET_DETAIL_CRD]
    @CMD_ID INT
AS
BEGIN;
    WITH T1(TCA_ID, CRD_RECHARGEABLE, MINIMA, MAXIMA)
    AS(
        SELECT
                CRD.TCA_ID,
                CRD.CRD_RECHARGEABLE,
                MIN(CRD_SERIAL) AS 'MINIMA',
                MAX(CRD_SERIAL) AS 'MAXIMA'
        FROM
                T_GIFT_COMMANDE_GFC GFC
                    INNER JOIN T_CARD_CRD CRD
                        ON    GFC.GFT_ID = CRD.GFT_ID     
        WHERE
                GFC.CMD_ID = @CMD_ID
        GROUP BY
                CRD.TCA_ID,
                CRD_RECHARGEABLE
    ),
    T2(TCA_ID, CRD_RECHARGEABLE, CRD_SERIAL)
    AS(
        SELECT
                CRD.TCA_ID,
                CRD.CRD_RECHARGEABLE,
                CRD.CRD_SERIAL
        FROM
                T_GIFT_COMMANDE_GFC GFC
                    INNER JOIN T_CARD_CRD CRD
                        ON    GFC.GFT_ID = CRD.GFT_ID 
                        INNER JOIN T1
                            ON T1.TCA_ID = CRD.TCA_ID     
        WHERE
                GFC.CMD_ID = @CMD_ID
    ),
    T3(TCA_ID, SER_ID)
    AS(
        SELECT
                TCA_ID,
                SER_ID
        FROM
                T_SERIAL_SER, T1
        WHERE
                SER_ID NOT IN (SELECT CRD_SERIAL FROM T2 WHERE TCA_ID = T1.TCA_ID)
            AND SER_ID BETWEEN T1.MINIMA AND T1.MAXIMA+1
    )
 
    ,
    T4(TCA_ID, CRD_RECHARGEABLE, MINIMA, MAXIMA, TROU)
    AS(
    SELECT 
            T2.TCA_ID,
            CRD_RECHARGEABLE,
            MIN(T2.CRD_SERIAL) AS 'MINIMA',
            MAX(T2.CRD_SERIAL) AS 'MAXIMA',
            MIN(T3.SER_ID) AS 'MIN'
    FROM
            T2 
                INNER JOIN T3 
                    ON    T2.CRD_SERIAL < T3.SER_ID 
                    AND    T2.TCA_ID = T3.TCA_ID 
    WHERE
            T2.CRD_SERIAL < T3.SER_ID 
    GROUP BY
            T2.TCA_ID,
            CRD_RECHARGEABLE,
            T2.CRD_SERIAL
    )
 
    SELECT
            TCA_ID, 
            CRD_RECHARGEABLE,
            MIN(MINIMA) AS 'MIN', 
            MAX(MAXIMA) AS 'MAX'
    FROM
            T4
    GROUP BY
            TCA_ID,
            CRD_RECHARGEABLE,
            TROU
END
GO

Qui se charge de récupérer les cartes-cadeau faisant partie d'une commande.
Et il se fait que cette requête pédale dans la semoule...
Je cherche à savoir quel index ajouter je m'y perds...

A côté de ça, j'ai la même requête mais qui va chercher les chèques-cadeau (table T_CHEQUE_CHQ) à la place des cartes. Rigoureusement la même requête mis à part les colonnes qui changent évidemment (mais vraiment pas grand chose). Et cette requête là donne un temps de réponse très satisfaisant...

La seule différence notable que je vois est que la table des cartes compte 39000 lignes et celle des chèques 15000 mais cela reste un petit nombre pour un SGDBR.

La table T_SERIAL_SER quant à elle compte 9 999 999 lignes (c'est juste une table des nombres entier de 1 à 9 999 999).

[iberserk]

Commencez par isoler les parties impactant vos performances...

Un petit coups d'oeil au plan d’exécution (postez le...) nous en dira plus

[Kropernic]

J'ai déjà essayer de regarder avec le plan d'exécution.

J'y ai bien vu des index scans mais pas de prédicat alors je ne sais pas quel index créé

Le plan d'exécution se trouve donc en pièce jointe dans une archive winrar (sinon le fichier est trop gros pour le site).

[dbaffaleuf]

Si je vous suis, suivant le nombre de colonnes se trouvant dans la clause SELECT, il peut être plus intéressant de lire directement toutes les lignes de la table pour directement tout avoir plutôt que de faire la recherche dans un index pour ensuite aller chercher dans la table les valeurs manquantes (via l'index cluster toujours si j'ai bien compris).

C'est exactement ça.

Pour le reste il faudrait exécuter cette procédure avec le plan d'exécution réel dans Management studio (Actual Execution Plan), sauvegarder le plan au format *.sqlplan et le poster. Et aussi :

Code :

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

1
2
3
4
5
6
set statistics io on
GO
set statistics time on 
GO
execute UP_COMMANDE_GET_DETAIL_CRD @CMD_ID=...
GO

[Kropernic]

Ah oui, c'est le plan estimé que j'ai posté...

Je vais tenter d'obtenir le réel (je n'ai jamais eu la patience de laisser la requête s'achever).

[Kropernic]

Alors je ne sais pas à quoi c'est du mais elle n'a mis "que" 2min19s pour s'exécuter..

En pièce jointe, le plan d'exécution réel et les messages renvoyés par les instructions suggérées par dbaffaleuf.


EDIT :
Une chose me vient à l'esprit et je me demande si cela ne pourrait avoir une influence...
Le fait est, qu'actuellement, cette DB est en local sur mon laptop car je suis en train de développer l'application qui "va avec" et que, pour bosser dans le train, c'est plus pratique d'avoir la DB en local plutôt que sur un serveur injoignable.
J'ai donc installer SQL SERVER 2008 express sur mon laptop qui tourne avec WIN7 et 6GB de RAM.

Il se trouve également que je ne redémarre que très rarement la machine car je préfère la mettre en veille au lieu de vraiment l'éteindre pour qu'elle démarre plus vite quand j'en ai besoin. Or, depuis que j'ai installé SQL SERVER, au bout d'un temps, je suis forcé de faire un restart de la machine car toute la machine pédale dans la semoule et, d'après les graphiques du Task Manager, j'ai supposé que cela était dû au fait que toute la RAM ou presque était utilisée.

Un SGDBR travaillant en RAM, j'ai supposé cela normal mais le fait d'avoir faire un restart hier soir, ajouté au fait que la procédure qui mettait plus de 30 min (sans en voir le bout) hier après-midi soit arrivée à son terme en 2min ce matin me pousse à dire qu'il y a p-e quelque chose de côté-là.

Est-il possible que j'aie un autre programme qui fagocite petit à petit la RAM dont SQL SERVER aurait besoin ?

[dbaffaleuf]

Je reverrais l'écriture en remplaçant les CTEs par des tables temporaires physiques. Pour deux raisons principalement: 1) je pense que la nature de la CTE force l'optimiseur à ne pas considérer autre chose que des jointures nested loops, et 2) parce qu'il ne dispose pas de statistiques précises et qu'il se plante dans le choix de ses opérateurs, par exemple ici (cf copie jointe).

[Kropernic]

Je vais tester cela.

Une précision par contre.

Pour parler de table temporaire qu'on peut créer avec le caractère # ou bien d'une "vraie" table que je peux retrouver dans la liste du management studio par après ? (il est évident qu'elle sera supprimée à la fin de requête, c'est juste pour l'exemple)

[dbaffaleuf]

Je vais tester cela.

Une précision par contre.

Pour parler de table temporaire qu'on peut créer avec le caractère # ou bien d'une "vraie" table que je peux retrouver dans la liste du management studio par après ? (il est évident qu'elle sera supprimée à la fin de requête, c'est juste pour l'exemple)

#temptable

[Kropernic]

ok, je teste cela de suite !

[Kropernic]

Voilà, le test est fait.

Je suis tombé à 48 secondes. C'est pas mal mais c'est quand même toujours pas mal lent pour l'utilisateur qui poireaute devant son écran. J'ai mis en pièce jointe le plan d'exécution réel et les messages obtenu par vos deux instructions.

ET SI...

Et si je créais des vues indexées ? Mais je suis encore plutôt noob de côté-là ^^.

Je vais essayer d'un peu analyser le bousin.

[iberserk]

de statistiques précises

Le problème ne vient'il pas simplement de là? vos statistiques sont'elles à jour et exécutées en FULLSCAN?

[dbaffaleuf]

Il faudrait créer un index sur #T2(CRD_SERIAL) INCLUDE (TCA_ID, CRD_RECHARGEABLE)

[Kropernic]

Le problème ne vient'il pas simplement de là? vos statistiques sont'elles à jour et exécutées en FULLSCAN?

je dois bien reconnaître que là, vous ne parlez plus la même langue que moi.

Tout ce que je sais en matière d'administration de DB, je l'ai appris en grande partie via les articles de sqlpro et je n'ai jamais reçu de réelle formation.

Alors oui, je sais que sql server fait des statistiques sur... tout un tas de choses mais cela s'arrête là jusqu'à présent.

Comment puis-je faire pour déterminer la réponse à votre question ?

[mikedavem]

Il faudrait créer un index sur #T2(CRD_SERIAL) INCLUDE (TCA_ID, CRD_RECHARGEABLE)

Je commencerai par cela pour voir moi aussi. Le but étant ici de supprimer les opérations Has Match (join et aggregate). Le Hash match (join) peut être évité si les données d'entrées sont déjà triées par exemple (donc par le biais d'un index)

++

[Kropernic]

Il faudrait créer un index sur #T2(CRD_SERIAL) INCLUDE (TCA_ID, CRD_RECHARGEABLE)

1 seconde avec l'ajout de l'index !!!



Je vais donc aller modifier la requête sur les cheques de la même manière histoire d'éviter que, plus tard, elle ne pédale également !

Y a-t-il des articles/tutos/autres sur les plans de requêtes pour savoir quelles actions entreprendre en les lisant ?

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