Création d'index : quelles colonnes ?

Version imprimable

Bonjour,

Je suis chargé d'extraire certaines données, par mois, d'une table contenant des données de ventes.

J'ai fait une procédure stockée et la résultat obtenu est ok. Par contre, j'aimerais accélérer le temps d'exécution est, pour le mois où il y a le plus de ventes, de 33 secondes.

Il s'agit d'une vieille table (probablement transposée en sql serveur depuis un ancien système de stockage sans repenser la structure) avec plein de colonnes (une table obèse je crois que ça s'appelle). Bref, j'ai déjà fait quelques aménagements (ajout d'un id et quelques index) qui rendent les recherches courantes suffisamment performante.

Par contre ici, je sèche un peu... J'ai toujours du mal à savoir quelles colonnes mettre dans l'index et quelles colonnes mettre dans la clause include.

Voici la table en question :

Code:

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
USE [Tickets]
GO
 
/****** Object:  Table [dbo].[tblTransactions]    Script Date: 08/30/2012 11:48:50 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
SET ARITHABORT ON
GO
 
CREATE TABLE [dbo].[tblTransactions](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [date_dt] [datetime] NULL,
    [year]  AS (left([date],(4))) PERSISTED,
    [date] [char](8) NOT NULL,
    [siege] [char](3) NOT NULL,
    [ray] [char](4) NULL,
    [type] [char](1) NULL,
    [eanart] [char](13) NOT NULL,
    [pu] [money] NOT NULL,
    [qt] [money] NULL,
    [ca] [money] NULL,
    [rab] [money] NULL,
    [escp] [money] NULL,
    [lm] [char](1) NULL,
    [nocre] [char](4) NULL,
    [caisse] [char](3) NOT NULL,
    [ticket] [char](4) NOT NULL,
    [cartebanque] [char](16) NULL,
    [carteclient] [char](16) NULL,
    [gib] [char](3) NULL,
    [time] [char](4) NULL,
    [typecart] [char](1) NULL,
    [typefim] [char](1) NULL,
    [typerab] [char](1) NULL,
    [typeray] [char](1) NULL,
 CONSTRAINT [PK_tblTransactions] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [INDEX]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO

Voici la requête :

Code:

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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
USE [Tickets]
GO
/****** Object:  StoredProcedure [dbo].[UP_TILL_ANALYSIS]    Script Date: 08/30/2012 11:48:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UP_TILL_ANALYSIS] 
@Strsiege char(3),@DtdebA Datetime,@DtfinA Datetime,@DtdebP Datetime,@DtfinP Datetime
AS
BEGIN
    SET NOCOUNT ON;
--année précédente
    with T1(siege, date, caisse, ticket, ca_brut, ca_net, tot_rab, nb_art)
    as(
        select
            siege,
            cast(floor(cast(date_dt as float)) as datetime) as 'date',
            caisse,
            ticket,
            sum(ca) as 'ca_brut',
            sum(ca-rab) as 'ca_net',
            sum(rab) as 'tot_rab',
            sum(qt) as 'nb_art'
        from
            dbo.tblTransactions
        where
            siege = @Strsiege
            and date_dt between @DtdebP and @DtfinP
        group by
            siege,
            cast(floor(cast(date_dt as float)) as datetime),
            caisse,
            ticket
        ),
 
        T2 (siege, date, caisse, etage, nb_ticket, sum_ca_brut, avg_ca_brut, sum_ca_net, avg_ca_net, sum_tot_rab, avg_tot_rab, nb_art, pu_moyen,actprev)
        as(
            select 
                t.siege,
                t.date,
                t.caisse,
                c.etage,
                count(*) as 'nb_ticket',
                sum(ca_brut) as 'sum_ca_brut',
                avg(ca_brut) as 'avg_ca_brut',
                sum(ca_net) as 'sum_ca_net',
                avg(ca_net) as 'avg_ca_net',
                sum(tot_rab) as 'sum_tot_rab',
                avg(tot_rab) as 'avg_tot_rab',
                sum(nb_art) as 'nb_art',
                sum(ca_brut)/case sum(nb_art) when 0 then 1 else sum(nb_art) end as 'pu_moyen',
                'P' as 'actprev'
            from 
                T1 T
                inner join tb_caisses C
                on    T.siege = C.siege
                and T.caisse = C.caisse
            group by
                t.siege,
                t.date,
                t.caisse,
                c.etage
            ),--année actuelle
 
     T3(siege, date, caisse, ticket, ca_brut, ca_net, tot_rab, nb_art)
     as(
        select
            siege,
            cast(floor(cast(date_dt as float)) as datetime) as 'date',
            caisse,
            ticket,
            sum(ca) as 'ca_brut',
            sum(ca-rab) as 'ca_net',
            sum(rab) as 'tot_rab',
            sum(qt) as 'nb_art'
        from
            dbo.tblTransactions
        WHERE
            siege = @Strsiege
            and date_dt between @DtdebA and @DtfinA
        group by
            siege,
            cast(floor(cast(date_dt as float)) as datetime),
            caisse,
            ticket
        ),
 
        T4 (siege, date, caisse, etage, nb_ticket, sum_ca_brut, avg_ca_brut, sum_ca_net, avg_ca_net, sum_tot_rab, avg_tot_rab, nb_art, pu_moyen,actprev)
        as(
            select 
                t.siege,
                t.date,
                t.caisse,
                c.etage,
                count(*) as 'nb_ticket',
                sum(ca_brut) as 'sum_ca_brut',
                avg(ca_brut) as 'avg_ca_brut',
                sum(ca_net) as 'sum_ca_net',
                avg(ca_net) as 'avg_ca_net',
                sum(tot_rab) as 'sum_tot_rab',
                avg(tot_rab) as 'avg_tot_rab',
                sum(nb_art) as 'nb_art',
                sum(ca_brut)/case sum(nb_art) when 0 then 1 else sum(nb_art) end as 'pu_moyen',
                'A' as 'actprev'
            from 
                T3 T
                    inner join tb_caisses C
                    on    T.siege = C.siege
                    and T.caisse = C.caisse
            group by
                t.siege,
                t.date,
                t.caisse,
                c.etage
            )
 
--query
select
        t2.siege,
        t2.date,
        t2.caisse,
        t2.etage,
        t2.nb_ticket,
        t2.sum_ca_brut,
        t2.avg_ca_brut,
        t2.sum_ca_net,
        t2.avg_ca_net,
        t2.nb_art,
        t2.pu_moyen,
        t2.sum_ca_net/t2.nb_ticket as 'ticket moyen',
        t2.actprev
from
        T2 
 
union all
 
select
        t4.siege,
        t4.date,
        t4.caisse,
        t4.etage,
        t4.nb_ticket,
        t4.sum_ca_brut,
        t4.avg_ca_brut,
        t4.sum_ca_net,
        t4.avg_ca_net,
        t4.nb_art,
        t4.pu_moyen,
        t4.sum_ca_net/t4.nb_ticket as 'ticket moyen',
        t4.actprev
from    
        T4
order by
        actprev,
        siege,
        etage,
        caisse,
        date
 
END

Et voici ce que j'ai créé comme index mais sans résultat. J'ai forcément du merdé quelque part...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
USE [Tickets]
GO
 
/****** Object:  Index [IX_TILL_ANALYSIS]    Script Date: 08/30/2012 11:49:43 ******/
CREATE NONCLUSTERED INDEX [IX_TILL_ANALYSIS] ON [dbo].[tblTransactions] 
(
    [date_dt] DESC,
    [siege] ASC
)
INCLUDE ( [caisse],
[ticket]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [INDEX]
GO

30/08/2012, 15h17
Kropernic

Après "maturation", ma question pourrait se résumer à :

Faut-il mettre les colonnes faisant l'objet de fonction d'agrégation dans l'index ?
30/08/2012, 15h51
SQLpro
Déjà ceci :
[year] AS (LEFT([date],(4))) PERSISTED,

donne des résuktat incohérent.

Ensuite il faudrait une colonne calculée persistante sur la date seule.
Code:

1 2 ALTER [dbo].[tblTransactions] ADD LaDate AS cast(floor(cast(date_dt AS float)) AS datetime) PERSISTED;
vous pourrez alors l'indexer !

A +
30/08/2012, 16h05
Kropernic

Ah ouais, ça je peux faire.

Sinon, entre temps, via votre script qui détecte les index manquant, j'ai ajouté les index qui concernaient la table en question et ça va déjà tout de suite mieux. Cela varie suivant la volume de données à rapatrier mais ça ne dépasse pas les 10 secondes.

Net progrès donc !

Je vais voir pour mettre la colonne calculée persistante que vous proposez :)

Néanmoins, quand est-il des colonnes subissant une agrégation ? Malgré que j'ai déjà lu 2 fois votre article sur l'indexaction, j'ai toujours du mal :-/
30/08/2012, 16h14
Waldar

L'idée d'éviter de passer par la table implique que toutes les colonnes contenues dans votre requête doivent exister dans l'index, de facto les colonnes agrégées également.

Les colonnes qui servent en filtre sont à indexer (ce que vous avez bien fait), les colonnes qui servent à l'affichage peuvent aller dans le include.

Regardez bien les plans d'exécutions, ce sont eux qui vont vous montrer par où passe le moteur SQL-Server afin de vous rapatrier les données.
30/08/2012, 16h43
Kropernic

En relisant une énième fois l'article de sqlpro sur l'indexation, un truc me chipote.

Sur une autre DB que je suis en train de développer (p-e pas le bon terme à utiliser), j'ai créé des vues en utilisant "with schemabinding". Dois-je créer des index sur ces vues également ou bien vont-elles utiliser les index créer sur les tables sous-jacentes ?

Une vue indexée n'a d'intérêt que si :
SOIT il y a un calcul d'agrégat de type SUM ou COUNT (AVG pouvant être simulé par une combinaison de SUM et COUNT).
SOIT il y a une jointure très distante entre deux informations à rapprocher (par exemple parcourir 30 tables pour aller de l'un à l'autre).

Les limitations des requêtes sont importantes et sont induites par le fait que :

on doit avoir une bijection entre les lignes des tables source et de la vue (ceci exclue donc les UNION, INTERSECT, EXCEPT, sous requêtes et jointures externes....)
on doit pouvoir mettre à jour les données de la vue par différence (donc déterministe et précis => pas de FLOAT ni REAL en particulier).

Faites ceci :
Code:

1 2 ALTER [dbo].[tblTransactions] ADD LaDate AS cast(floor(cast(date_dt AS float)) AS datetime) PERSISTED;
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE VIEW dbo.V_tblTransactions WITH SCHEMABINDING AS SELECT siege, LaDate, caisse, ticket, sum(ca) AS 'ca_brut', sum(ca-rab) AS 'ca_net', sum(rab) AS 'tot_rab', sum(qt) AS 'nb_art' FROM dbo.tblTransactions GROUP BY siege, LaDate, caisse, ticket;
Code:

1 2 3 CREATE UNIQUE CLUSTERED INDEX X_V_tblTransactions ON dbo.V_tblTransactions (siege, LaDate, caisse, ticket);
Puis récrivez vos requêtes avec la vue et dites moi ce que vous en pensez !

Je ne serais pas étonné à ce que vous tombiez à moins d'une seconde....

A +

31/08/2012, 09h50
Kropernic

J'ai voulu ajouté la colonne calculée que vous suggérez mais je reçois le message suivant :

Citation:

Msg 4936, Level 16, State 1, Line 1
Computed column 'LaDate' in table 'tblTransactions' cannot be persisted because the column is non-deterministic.

Je comprends le message mais je n'en comprends pas la cause.

EDIT : En fait je ne comprends pas pourquoi ce n'est pas déterministe ? Je vais tenter de trouver une définition de ce terme. Ce sera p-e plus clair alors.

EDIT 2 : Après avoir lu la définition, je ne comprends pas pourquoi ce calcule n'est pas déterministe...

Lancez exactement ce script :

Code:

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
CREATE TABLE [dbo].[tblTransactions](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [date_dt] [datetime] NULL,
    [year]  AS (LEFT([date],(4))) PERSISTED,
    [date] [char](8) NOT NULL,
    [siege] [char](3) NOT NULL,
    [ray] [char](4) NULL,
    [type] [char](1) NULL,
    [eanart] [char](13) NOT NULL,
    [pu] [money] NOT NULL,
    [qt] [money] NULL,
    [ca] [money] NULL,
    [rab] [money] NULL,
    [escp] [money] NULL,
    [lm] [char](1) NULL,
    [nocre] [char](4) NULL,
    [caisse] [char](3) NOT NULL,
    [ticket] [char](4) NOT NULL,
    [cartebanque] [char](16) NULL,
    [carteclient] [char](16) NULL,
    [gib] [char](3) NULL,
    [time] [char](4) NULL,
    [typecart] [char](1) NULL,
    [typefim] [char](1) NULL,
    [typerab] [char](1) NULL,
    [typeray] [char](1) NULL,
 CONSTRAINT [PK_tblTransactions] PRIMARY KEY 
(
    [id] ASC
)
) ON [PRIMARY]
GO
 
ALTER TABLE [dbo].[tblTransactions]
ADD LaDate AS cast(floor(cast(date_dt AS INT)) AS datetime) PERSISTED;
GO
 
CREATE VIEW dbo.V_tblTransactions
WITH SCHEMABINDING
AS
SELECT
            siege,
            LaDate,
            caisse,
            ticket,
            sum(ISNULL(ca, 0)) AS 'ca_brut',
            sum(ISNULL(ca-rab, 0)) AS 'ca_net',
            sum(ISNULL(rab, 0)) AS 'tot_rab',
            sum(ISNULL(qt, 0)) AS 'nb_art',
            COUNT_BIG(*) AS N
        FROM
            dbo.tblTransactions
        GROUP BY
            siege,
           LaDate,
            caisse,
            ticket;
GO
 
CREATE UNIQUE CLUSTERED INDEX X_V_tblTransactions 
   ON dbo.V_tblTransactions           
   (siege, LaDate, caisse, ticket);
GO

Au passage :
1) faire une PK non clustered alors que c'est une unique colonne IDENTITY est ahutement stupide !
2) si vous le pouvez placez les colonnes ca, rab, qt en NOT NULL DEFAULT 0

L'erreur était dû au fait que j'utilisait un FLOAT. Vérifiez que le INT ne pose pas de problème notamment lors des dateheure d'après midi.

A +

31/08/2012, 10h15
StringBuilder
Citation:
Envoyé par SQLpro

on doit pouvoir mettre à jour les données de la vue par différence (donc déterministe et précis => pas de FLOAT ni REAL en particulier).

Faites ceci :

Code:

1 2 ALTER [dbo].[tblTransactions] ADD LaDate AS cast(floor(cast(date_dt AS float)) AS datetime) PERSISTED;
J'ai l'impression que d'après le premier point, SQL Server estime que le type FLOAT n'est pas précis, et donc éventuellement, non déterministe. (A vérifier avec SQLpro).

Je tenterais donc un truc du genre :
Code:

1 2 3 ALTER tblTransactions ADD LaDate AS cast(cast(date_dt AS int) AS datetime) PERSISTED;
Ca devrait faire la même chose, mais sans passer par le type float qui pourrait être la source du problème.

-- Grillé
31/08/2012, 10h17
Kropernic

Citation:

Envoyé par SQLpro

Au passage :
1) faire une PK non clustered alors que c'est une unique colonne IDENTITY est ahutement stupide !

Les recherches ne se faisant jamais sur l'id, l'index cluster a été placé sur les colonnes date_dt et id. Ce n'est pas bon ? (cela avait été conseillé dans une formation que mon collègue et moi avons suivie en décembre.)

J'imagine qu'avec INT au lieu de FLOAT, la fonction FLOOR n'est plus nécessaire non ?
31/08/2012, 10h20
StringBuilder

Pour le cluster sur le tuple date/id, il faut être absolument certain dans ce cas que la date est immuable, et est bien un "timestamp", pas une simple valeur.

En effet, si vous produisez des insertions/mises à jour sur ce champ, SQL Server va passer des heures à décaler physiquement toutes les lignes dans la table.

C'est pour ça qu'il est très fortement recommandé de mettre l'index cluster sur l'identifiant auto-incrément qui est immuable (et donc pas d'insertion entre deux lignes existantes)
31/08/2012, 10h27
Kropernic

Citation:

Envoyé par StringBuilder

Pour le cluster sur le tuple date/id, il faut être absolument certain dans ce cas que la date est immuable, et est bien un "timestamp", pas une simple valeur.

En effet, si vous produisez des insertions/mises à jour sur ce champ, SQL Server va passer des heures à décaler physiquement toutes les lignes dans la table.

C'est pour ça qu'il est très fortement recommandé de mettre l'index cluster sur l'identifiant auto-incrément qui est immuable (et donc pas d'insertion entre deux lignes existantes)

Ok je crois que je comprends. Pour être sûr, si j'insère toutes les ventes du magasin X pour le une journée Y et que après, j'insère toutes les ventes du magasin Z pour la même journée Y, là on tombe dans ce que vous décrivez. Est-ce bien cela ? Si oui, je vais changer les choses ^^ (en espérant que j'ai assez d'espace disque pour tout faire... hier j'ai voulu ajouter les index manquants à l'aide du script de sqlpro et je n'ai pas pu arriver au bout...)
31/08/2012, 10h37
Kropernic

Citation:

Envoyé par SQLpro

L'erreur était dû au fait que j'utilisait un FLOAT. Vérifiez que le INT ne pose pas de problème notamment lors des dateheure d'après midi.

A +

Effectivement, si on cast en int,pour les dateheure de l'après-midi, c'est arrondi au jour d'après...

Ce qui n'est bien sûr pas bien dans mon cas ^^.

On a le droit de mettre un IF dans l'expression d'une computed-column ?
31/08/2012, 10h44
SQLpro

Il faut donc mettre CEILING ou FLOOR en sus...

A +
31/08/2012, 10h50
Kropernic

Bin je veux bien mais je ne vois pas trop où/comment...

Si je prends :

Code:

cast(floor(cast(date_dt AS INT)) AS datetime)

J'ai bien l'arrondi. Normal vu que la conversion en INT est fait avant FLOOR.
Et faire un floor sur un type datetime, ça ne marche pas...

Sinon j'ai songé à utiliser la fonction DATEADD pour enlever la partie time de la donnée mais c'est p-e bcp de chipotage...
31/08/2012, 11h01
Kropernic

Je pense finalement utiliser ceci :

Code:

cast(cast(DATEADD(hour,-12,date_dt) as int) as datetime)

Qu'en pensez-vous ?

En enlevant 12h à la valeur de date_dt :
- si c'est une heure de l'après-midi, ça deviendra une heure du matin et j'aurai donc bien la bonne date en castant en INT ;
- si c'est une heure du matin, ça deviendra une heure de l'après-midi de la veille et sera donc arrondi à la bonne date en castant en INT.

EDIT : Toujours ce problème de déterminisme. Pourtant, selon la MSDN, DATEADD est une fonction déterministe. Le problème viendrait-il du cast ? Il est pourtant évident qu'on peut prévoir l'output sur base de l'input (c'est en gros la définition que j'ai trouvé sur le net).
31/08/2012, 11h09
Kropernic

Trouvé à l'instant sur la msdn :

Citation:

CAST
Deterministic unless used with datetime, smalldatetime, or sql_variant.

Donc je crois que c'est mort pour cette colonne calculée...
31/08/2012, 12h08
Kropernic
J'y suis parvenu en "trichant".

Il se trouve que dans cette table se trouve la colonne [date] en char(8):aie: (c'est un vieil héritage mais des applications fonctionnent encore avec :cry:)

J'ai donc fait ceci :
Code:

1 2 3 4 ALTER TABLE [dbo].[tblTransactions] ADD LaDate AS convert(datetime, [date], 112) PERSISTED; GO
Et là, ça fonctionne.

N.B. : A noter aussi que
Citation:
CAST
Deterministic unless used with datetime, smalldatetime, or sql_variant.
CONVERT
Deterministic unless one of these conditions exists:

Source type is sql_variant.
Target type is sql_variant and its source type is nondeterministic.
Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.
03/09/2012, 12h51
Kropernic

Ma question initiale concernant les colonnes à mettre dans l'index ayant trouvé sa réponse, je clos le topic.

Merci à tous !