MIN = TOP 1 avec index cluster ?

**Kropernic** · 08/11/2012, 10h00

Bonjour,

En considérant la table dont voici le DDL

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
 
USE [GIFT_MANAGEMENT]
GO
 
/****** Object:  Table [dbo].[T_SERIAL_SER]    Script Date: 11/08/2012 09:52:29 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[T_SERIAL_SER](
    [SER_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_T_SERIAL_SER] PRIMARY KEY CLUSTERED 
(
    [SER_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO

Est-il correct de faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT TOP 1
        SER_ID AS 'CRD_SERIAL'
FROM
        DBO.T_SERIAL_SER SER
WHERE
        SER_ID > @UNE_VALEUR 
    AND SER_ID NOT IN    (
                        @X,@Y,@Z
                        )

Plutôt que :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
        MIN(SER_ID) AS 'CRD_SERIAL'
FROM
        DBO.T_SERIAL_SER SER
WHERE
        SER_ID > @UNE_VALEUR 
    AND SER_ID NOT IN    (
                        @X,@Y,@Z
                        )

Alors je sais qu'il n'y a pas d'ordre dans une table. Que c'est un sac de billes. Mais le fait d'introduire un index cluster n'apporte-t-il pas de l'ordre ?

Je pose cette question car cette petite requête toute simple s'intègre dans en fait dans une plus grande et est le membre de droite d'une clause OUTER APPLY. Le truc est qu'avec le 2e bout de code, je ne vois jamais le bout de la requête... Alors qu'avec le 1e, c'est immédiat ou presque.

Pour info, voici la requête complete :

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 TOP 1
                                    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]

**StringBuilder** · 08/11/2012, 11h05

Non, ce n'est pas correct.

Rien n'empêche ton SGBD de lire l'index à l'envers s'il a envie.
Ou d'utiliser un autre index que celui de la PK.

Le TOP 1 ID est équivalent au MIN(ID) si et seulement si le TOP 1 ID est associé à un ORDER BY ID ASC

**Kropernic** · 08/11/2012, 11h09

J'ai bien précisé que je posais la question dans le cadre de la table dont j'ai posté le DDL.

Donc pas d'autre index possible que celui cluster.

Ca arrive vraiment que le SGBDR lise un index à l'envers ? 0_0

Quant à la clause order by, n'est pas justement implicite à cause de l'index justement ?

**StringBuilder** · 08/11/2012, 11h21

Je suis absolument certain, surtout à cause de la jointure, que rien ne garanti que les lignes seront retournées dans l'ordre de l'index de ta table 1, car si c'est la table2 qui est lue en premier (et donc les lignes retournées dans l'ordre de table2) alors les lignes de table1 seront dans le désordre.

PS : Et le "not in" peut être traduit par le SGBD par une jointure.

Ensuite, ton DDL est ce qu'il est aujourd'hui. Rien ne garanti que demain il n'y aura pas une autre colonne.
(ni même que l'index en cluster sera différent demain)

Et une requête qui change de résultat quand mon modifie un index ou un type de colonne, bah... non quoi... Je sais bien qu'il faut donner du boulot à l'équipe support mais quand même

**Kropernic** · 08/11/2012, 11h26

Mmh ok...

Auriez-vous une vague idée du/des index/es que je devrais créer pour optimiser la vitesse de traitement de cette requête ?

J'ai toujours du mal pour déterminer cela... (le manque de pratique).

Sinon, je vais tacher de cogiter pour modifier la requête... Il y a p-e moyen de faire plus simple...

**StringBuilder** · 08/11/2012, 11h30

C'est quoi l'algo de la requête ?

Car j'avoue que je m'y perd un peu...

**Kropernic** · 08/11/2012, 11h58

C'est vrai que c'est un peu tordu et pour être franc, je ne comprends qu'à moitié ce que je fais.

Je me base en fait sur une requête proposé par iberserk pour un autre problème dont le résultat voulu est du même acabit.

Et donc l'idée de cette requête est de récupérer la liste des gifts qui ont été commandés (table T_GIFT_COMMANDE_GFC) et qui sont des cartes (table T_CARD_CRD) pour la commande X

A priori, pas de souci, un simple select avec une jointure entre les deux tables et le numéro de la commande dans le where et c'est beau.

Oui et non. Le souci est que dans une commande, je peux avoir des milliers de cartes (ou de cheque car j'ai la même requête avec la table T_CHEQUE_CHQ). Et afficher ligne par ligne chaque carte commandé, s'il y en a plusieurs milliers, il ne va juste pas regarder... D'autant plus qu'il est possible que les cartes commandés sont par exemple :

Pour le type (TCA_ID) n°9, du numéro (CRD_SERIAL) 1 à 500 et du numéro 700 à 1000.

Avec ça, afficher toutes les lignes dans une grille, l'utilisateur ne verra même pas qu'il y a un trou.

Du coup, plutôt que d'obtenir 801 lignes pour cette requête, je n'en veux que 2 qui serait ceci (sorry pour l'alignement des colonnes, suis pas doué

) :

Code :

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

1
2
3
4
 
TCA_ID    |    FROM    |    TO
9     |   1    |    500
9     |   700    |    1000

C'est là qu'intervient la clause OUTER APPLY.
J'avoue que je ne comprends pas bien comment cet opérateur fonctionne (je sais que ça fait qqch ligne par ligne ce n'est pas encore clair) mais d'après les explications de iberserk sur l'autre discussion, cette partie sert à localiser le trou.
J'ai donc fait en sorte d'obtenir le plus petit numéro qui ne se trouve pas dans la liste des numéros de série de cette commande et qui est en même temps supérieur au numéro de série (de la ligne en cours avec outer apply si je comprends bien).

J'ai donc créé la table T_SERIAL_SER qui contient les entiers de 1 à 9999999 afin d'avoir une référence pour trouver les numéros manquant.
C'est car il y a un si grand nombre de lignes dans cette table que faire une fonction d'agrégation prends super longtemps à cause de l'opérateur > qui se trouve dans le where. D'où mon TOP 1 car selon moi, l'index cluster ordonne la table et donc en prenant le premier plus grand, j'ai donc le plus petit des plus grands (je suis presque certain que je ne suis pas clair

)

**Kropernic** · 08/11/2012, 12h21

Pour info, je suis arrivé à produire la même chose que la requête tarabiscotée en faisant ceci (y a 2 fois plus de code mais c'est plus structuré et sans clause TOP)

N.B. : ici pour la commande n° 9

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
 
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 = 9
    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 = 9
),
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

J'ai joint le plan d'exécution réelle de la requête.

On y voit des clusters index scan. Comment les éviter ? J'aurais bien créé l'index nécessaire mais je ne vois aucun prédicat quand je passe la souris dessus...

**SQLpro** · 08/11/2012, 13h17

Envoyé par Kropernic

Ca arrive vraiment que le SGBDR lise un index à l'envers ? 0_0

Non seulment il peut lire à l'envers, mais vu que c'est multithreadé il peut faire n lectures partielles...

On ne mélange JAMAIS le physique (index) avec la logique (requête), c'est au moteur de décider quel index il doit utiliser.

A +

**Rei Ichido** · 08/11/2012, 13h28

J'ajouterai qu'en plus de tout ce qui a été déjà dit, il y a là une sous-requête, ce qui a des chances de produire un hash join, donc un ordre totalement aléatoire.

**iberserk** · 08/11/2012, 14h42

Au passage vous parlez de l'utilisation de OUTER APPLY dans votre premier POST mais dans ce même POST vous collez une requête usant de CROSS APPLY ce qui est complétement différent!

**Kropernic** · 08/11/2012, 14h44

Ah ? Oups

.

C'est que vu que je pige pas trop comment ça marche, de temps en temps je remplace OUTER par APPLY pour tester et voir le résultat que ça donne pour tenter de comprendre ce qu'il se passe.

J'ai plus que probablement dû oublié de remettre OUTER avant de poster ici.

Sorry. (je vais donc éditer le premier message)

MIN = TOP 1 avec index cluster ?

Développement SQL Server

Discussions similaires

Partager

Partager