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

[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