Optimization de l'Indexation dans une table

Version imprimable

Bonjour,
J'ai une table volumineuse contenant l'historique des ventes d'une société (13 gigas)
La table est constituée ainsi
VENTES(JOUR, ARTICLE, MAGAZIN, VOLUME, PROFIT)

Donc des enregistrements de cette forme:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
20080101    0000000001    1      15800      78340.00
20080102    0000000001    1      23306      45094.10
20080103    0000000001    1	115032     178340.33
20080104    0000000001    1      15800      78340.00
20080105    0000000001    1      23306      45094.10
20080106    0000000001    1      15800      78340.00
20080107    0000000001    1      23306      45094.10
20080101    0000000002    1     215500      78340.00
20080102    0000000002    1      13307      45094.10
20080103    0000000002    1	115033     178340.33
20080104    0000000002    1      16602      78340.00
20080105    0000000002    1      23309      45094.10
20080106    0000000002    1      14808      78340.00
20080107    0000000002    1      11377      45094.10

Pour l'info, je n'ai que 2 magazins (1 et 2) donc je peux avoir des enregistrements similaires pour le magazin 2

Sans indexes, les performances sont catastrophiques.

J'ai donc du créer quleques uns

Code:

1
2
3
 
CREATE X_ARITCLE on VENTES(ARTICLE)
CREATE X_JOUR on VENTES(JOUR)

Ca a nettement amélioré les performances, mais je suis quasi sur que ce que je fais n'est pas optimal.

Des suggestions ?

Merci

31/08/2008, 12h57
ylarvor

Première suggestion : si vous voulez vraiment améliorer vous meme vos performances et si vous avez du temps, allez donc sur le site de sql pro pour lire ses tutorials sur l'indexation ( http:\\sqlpro.developpez.com )

Deuxième suggestion : une indexation n'a de sens que par rapport à une requête. Indiquez nous la ou les requêtes qui sont les plus longues et nous vous diront quels index placés pour réduire ce temps.
( Remarque : une indexation n'est pas gratuite et cela prend de la place en disque dur! )
Pour commencer, vous pouvez indexer la clé primaire et les clefs étrangères si ce n'est pas déjà fait.

Code:

1 2 3 4 5 6 7 8 select CATEGORIE, prod.article, magazin, COUNT(V.Jour) as days_of_sale into temp_daysOfSale from prod left join VENTES V on V.article = prod.article where convert(numeric(10,2), volume) > 0 group by CATEGORIE, prod.article, magazin order by CATEGORIE, prod.article, magazin
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 select CATEGORIE, ARTICLE, ARTICLE_NAME, MAGAZIN, count(jour) as days_on_prom into temp_daysOnProm from ( select distinct CATEGORIE, Hs.ARTCILE, P.ARTICLE_NAME, S.prom_id, S.prom_st_date, S.magazin, JOUR, prom_end_date from prod P inner join VENTES Hs on Hs.ARTICLE = P.ARTICLE inner join PromotionArticle S on S.ARTICLE = P.ARTICLE and s.magazin = Hs.magazin inner join PromotionHeader Ph on S.prom_id = Ph.prom_id and S.prom_st_date = Ph.prom_st_date and S.magazin = Ph.magazin where convert(numeric(10,2), volume) > 0 ) K where K.day between K.prom_st_date and K.prom_end_date group by CATEGORIE, ARTICLE, ARTICLE_NAME, MAGAZIN
Pour info :
Prod (categorie, article, article_name)
PromotionArticle(Article, prom_id, prom_st_date, magazin)
PromotionHeader (prom_id, prom_name, prom_st_date, prom_end_date, magazin)

Une promotion est définie par (prom_id, prom_st_date, magazin)

Prod posséde un index sur article (primary key)
PromotionArticle posséde un index sur (Article) et sur (prom_id, prom_st_date, magazin)
PromotionHeader posséde un index sur (prom_id, prom_st_date, magazin)

Ces 2 requêtes sont les plus couteuses en terme de performance ..

31/08/2008, 13h45
SQLpro

Quel est l'intérêt de :

Code:

convert(numeric(10,2), volume) > 0

?
Une fonction empêche toute optimisation. Si votre colonne volume est déjà un nombre, cela est stupide.

De plus il nous faut la decription sous forme CREATE TABLE de cotre table pour savoir les types de données et les contraintes (PK, FK, UNIQUE...)

A +
31/08/2008, 13h57
ylarvor
Je me concentre sur la table VENTES.
Code:

1 2 VENTES(JOUR, ARTICLE, MAGAZIN, VOLUME, PROFIT)
Je ne comprend pas que VOLUME ne soit pas du type NUMERIC ? Quel est le type de ce champs ?

Pour utiliser un index, il faudrait écrire WHERE Volume > 0 car les fonctions ne sont pas acceptées par les index. Peut être que à votre place, je créerais une colonne calculée de type numeric que j'adjoindrais à la table VENTES pour accélérer la requête, j'utiliserais alors cette nouvelle colonne numérique dans la requête.

Autre point ennuyeux, vous utilisez MAGAZIN Dans group by et order by, c'est donc une colonne qui doit être indexée mais elle ne prend que 2 valeurs ce qui la rend peu intéressante pour une indexation!

Créer un index sur le MAGAZIN, tester et regarder si cela est intéressant ou sans apport en terme de performance et gourmand en mémoire...
31/08/2008, 14h32
freestyler

ma table VENTES est d'origine un text file que j'importe à l'aide d'un package.
Le champ volume contient parfois des anomalies qui font échouer l'import, c'est pour cela que j'ai opté pour un champ nvarchar que je convertis au besoin..

Jai pas de clé primaire dans ma table Ventes. Vous me direz c'est pour ça que les performances sont désastreuses. Je réponds sur ça que je fais une analyse de données sur cette table qui contient parfois des doublons que je dois traiter et non supprrimer..

sinon : article, jour, magazin (INT).. Volume et profit (nvarchar(50))
31/08/2008, 15h33
SQLpro

Citation:

Jai pas de clé primaire dans ma table Ventes. Vous me direz c'est pour ça que les performances sont désastreuses. Je réponds sur ça que je fais une analyse de données sur cette table qui contient parfois des doublons que je dois traiter et non supprrimer..

Commencez par ajouter une colonnes auto incrément et mettez la en primary key après avoir inséré les données.

Rajoutez une colonne VOLUME_REAL en type FLOAT et basculez les données de volume dedans. Supprimez l'ancienne. Indexez la nouvelle et faites vos requêtes dessus.

Vous ne nous avez toujours pas dit la composition des tables !

Citation:

article, jour, magazin (INT).. Volume et profit (nvarchar(50))

De quelle table ? Et l'autre ???

Comment voulez-vous que l'on vous aide si vous ne répondez pas à nos demandes ???

A +
31/08/2008, 16h38
freestyler

tous les autres champs sont des INT. (à part article_name, et prom_name biensur qui sont des nvarchar(50))
Article est une clé primaire pour prod
(Article, prom_id) est une clé primaire pour PromotionArticle
(Prom_id, prom_st_date, estate) est une clé promaire pour PromotionHeader

C'est tout..
31/08/2008, 17h17
ylarvor

prpposition pour la table VENTES ( optimisation )

Citation:

J'ai une table volumineuse contenant l'historique des ventes d'une société (13 gigas)

Avec une table de 13 gigas, vous auriez intérêt à passer par une table temporaire lors de votre importation ( PREVENTES ) et de structurer la table VENTE comme une table définitive avec un index cluster sur la colonne MAGAZIN puisque vous n'en n'avez que deux et un index non cluster sur la colonne numérique VOLUME et comme vous l'aviez compris, un index non cluster sur la colonne ARTICLE.

La clé primaire IDENTITY que recommande sql pro peut être utile à condition d'utiliser la clé dans vos développements. Dans ce cas là, oubliez l'index cluster sur MAGAZIN puisqu'une clé primaire est aussi un index cluster.