Discussion :

[mioux]

Bonjour

Dans le cadre de l'optimisation d'une procédure stockée, j'ai ajouté un index sur une table contenant des données temporaire (mais utilisées une fois la PS terminée, donc pas de table #temporaire).

Je me demande si l'ajout de cet index n'est pas contre-performant dans la durée. En effet, la table est vidée plusieurs fois, et des INSERT de petites quantités de données sont réalisés entre 2 vidage de table. Je n'ai pas créé cette procédure, et je ne sais pas exactement ce qu'elle fait (je pense que revoir le process serait plus efficace que l'ajout d'index, mais je n'ai pas assez de vue sur ce que la procédure doit réaliser exactement pour m'amuser à la réécrire).

Le truc, c'est que cette procédure doit traiter des données pour 27 sociétés, données je suppose de même volumétrie pour chacune des sociétés.

8 sociétés ont étés traitées en 1h30 (au lieu de 2h auparavant), mais il a fallu 17h pour traiter l'ensemble des 27 sociétés (alors que je m'attendais à 6h de traitement)

Je n'ai pas de trace, et pas moyen de relancer le traitement, car maintenant qu'il est fini, la procédure ne fait rien. Toutefois, je peux voir que l'index est relativement fragmenté. Voici le résultat du SHOWCONTIG de l'index que j'ai ajouté :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
DBCC SHOWCONTIG scanning 'VP_T_COD00140_01' table...
Table: 'VP_T_COD00140_01' (1383064063); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 124
- Extents Scanned..............................: 20
- Extent Switches..............................: 19
- Avg. Pages per Extent........................: 6.2
- Scan Density [Best Count:Actual Count].......: 80.00% [16:20]
- Extent Scan Fragmentation ...................: 45.00%
- Avg. Bytes Free per Page.....................: 8062.1
- Avg. Page Density (full).....................: 0.39%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Du coup, je me demande si c'était une bonne idée de rajouter cet index, car au final, j'ai "perdu 9h" (sans les index, la durée estimée était de 8h, mais je met entre guillemet car on a pas laissé la procédure tourner complètement sans l'index).

Evidemment, je n'ai pas non plus de trace de temps étape par étape pour voir où ou quand (au niveau des boucles, voir si le traitement des sociétés est de plus en plus lent ou si c'est constant) j'ai perdu du temps, donc je ne sais même pas si c'est la partie que j'ai optimisée qui devait l'être ou une autre (oui on m'a demandé de travailler sur une partie seulement de la procédure, je n'ai pas analysé la procédure complète, j'ai fait confiance aux devs d'origine quand ils m'ont dit que c'est cette partie qui déconne).

[Waldar]

Les index sont utiles pour les lectures, mais ralentissent les écritures.

Une pratique courante quand on traite de nombreuses données, c'est de supprimer les index, vider la table, remplir la table, recréer les index.

Essayez peut-être avec cette méthode.

[mioux]

En fait, l'index a été mis, car après chaque petite insertion, il y a énormément de lectures qui sont faites, pour 1 insert, j'ai 5 ou 6 SELECT derrière (et je compte que ceux que je vois, car en plus, ils sont dans des boucles). C'est justement parce qu'il y avait beaucoup de SELECT que j'ai opté pour l'index, sinon c'est exactement ce que je fais, travailler sur une table non indexée, puis la réindexer une fois le traitement terminé.

Et malheureusement, je n'ai pas d'évaluation du temps passé sur la même table sans index, car on n'a pas laissé le traitement tourner jusqu'au bout.

[Waldar]

Dans ce cas il faut détailler plus ce qui est fait, est-ce qu'on ne peut pas remplacer ces traitements unitaires par un seul traitement ensembliste par exemple.

[mioux]

Ca, ça fait partie du travail en profondeur que je dois effectuer :-D

Et avec ce que j'ai entrevu, je pense que oui, il y a de l'optimisation à faire. J'ai vu beaucoup de

Code :

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

1
2
3
4
SELECT  a.c1, b.c2
FROM    a, b, c
WHERE   a.c1 = b.c1 AND
        b.c2 = c.c2

qui pourraient déjà être remplacé par de plus élégants INNER JOIN, quelques incohérences genre

Code :

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

1
2
3
4
UPDATE  a
SET     c1 = 0
WHERE   c2 = 25 AND
        c2 = 'toto'

qui ne fait donc strictement rien (mais je pense à un copier/coller malheureux) et aussi des updates avec un WHERE toujours vrai, car les données qui ont étés insérées à la base ont mit une valeur fixe pour tout le monde dans une colonne (et du coup, je me demande si elle est vraiment utile)

[elsuket]

Bonjour,

Les index sont utiles pour les lectures, mais ralentissent les écritures.

Ils les ralentissent mais les aident aussi :
- sur un INSERT, un index aide le moteur de stockage à savoir rapidement où il doit stocker la ligne
- sur un UPDATE ou DELETE, ils aident à qualifier les lignes qui vont être mises à jour ou supprimées.

Par expérience, pour avoir vu des tables avec un nombre excessif d'index, j'ai été surpris de voir qu'en écrire le tout fonctionnait plutôt pas mal ...

Comme d'habitude, il faut équilibrer la balance : ne créer que les index nécessaires, et auditer régulièrement les performances

et je compte que ceux que je vois, car en plus, ils sont dans des boucles

Comme le dit Waldar, c'est très probablement l'origine du problème.
Si la base de données est correctement modélisée, l'expression des requêtes est simple et ne nécessite pas de boucles.

Les boucles sont pour SQL totalement contre-performantes : SQL est un langage ensembliste, donc il est conçu pour traiter des données dans leur ensemble, et non pas tuple à tuple

D'autre part SQL est un langage déclaratif : c'est à dire que ce n'est pas au développeur d'indiquer au moteur de base de données comment il doit réaliser un traitement.
Il se contente simplement d'exprimer le résultat à obtenir, et le moteur de base de données fait le reste.
Une optimisation des performances d'une requête est parfois nécessaire, mais ça c'est le boulot d'un DBA.
En gros, vous êtes entre deux chaises

Les commentaires qui accompagnent les deux derniers bouts de code sont absolument corrects

@++