Demande d'aide pour l'optimisation d'une requête

[Menoto]

Bonjour,

j'ai un problème assez ennuyeux. J'ai développé une requête qui est très longue à l'exécution, 1min10 pour retourner 4000 lignes.
Le SGBD utilisé est SQL Server 2005.
Je sais que ce post est assez long mais je pense que c'est nécessaire pour bien décrire les choses.
Je vous remercie par avance du temps que vous aurez bien voulu prendre pour m'aider.

Description du système :
J'ai joins le MPD à ce post.
Le modèle porte sur le décapage de caisses de pièces en métal. Elles sont trempées dans un bac contenant une solution chauffée à une certaine température et contenant un certain taux d’acides.
Plusieurs caisses (Table : T_CAISSE_CSS) sont ajoutées dans un plan de décapage (Table : T_PLAN_DECAPAGE_PDC). Chaque plan de décapage correspond à une température de chauffe donnée et un taux d’acide défini. Chaque plan possède un statut (Table : TR_STATUTPLAN_SPL) parmi « Initialisé », « Préparé », « En cours », « Terminé », « Interrompu ».
Normalement, une caisse n’est plongée qu’une fois dans un bac mais il peut arriver qu’il faille la plonger une deuxième fois. Dans ce cas, elle sera ajoutée dans un autre plan de décapage avec des paramètres différents.
La table TJ_CAISSE_DECAP_CDC est la table de jointure des tables T_CAISSE_CSS et T_PLAN_DECAPAGE_PDC.
Chaque caisse possède un statut (Table : TR_STATUTCAISSE_STT) parmi : « Non décapé », «Réalisation », « Fini », « Quarantaine », « Prêt ».
Ce statut est repris dans la table de jointure afin de connaître l’historique de décapage de la caisse dans le plan de décapage.
Enfin pour terminer, une mesure est effectuée sur quelques caisses présentes dans le plan de décapage afin de vérifier si le décapage est correct. Ceci est représenté par la table T_MESURE_MES.

Problème :
J’ai réalisé plusieurs requêtes de sélection :
- sélectionner les plans de décapages possédant des caisses non expédiées
- sélectionner toutes les caisses appartenant à ces plans de décapages (même celle qui sont expédiés) et toutes les mesures associées.
J’ai donc réalisé une vue pour la sélection des plan de décapages :
Vue de sélection des plans de décapages possédant des caisses non expédiées :
(1)

Code sql :

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW V_PLAN_CSS_NEXPED
AS
SELECT	PDC_ID, PDC_REFERENCE, PDC_DATE_CREAT, PDC_HEUR_DEBUT, PDC_HEUR_FIN, PDC_TX_ACID, PDC_TEMPER_CHAUFF, PDC.SPL_PLN_CODE, SPL_PLN_LIBELLE
FROM		T_PLAN_DECAPAGE_PDC PDC
	INNER JOIN TJ_CAISSE_DECAP_CDC CDC
		ON PDC.PDC_ID = CDC.PDC_ID
	INNER JOIN T_CAISSE_CSS CSS
		ON CSS.CSS_ID = CDC.CSS_ID
	INNER JOIN TR_STATUTPLAN_SPL SPL
		ON SPL.SPL_PLN_CODE = PDC.SPL_PLN_CODE
WHERE	CSS.CSS_DATE_EXP IS NULL

Voici la requête de sélection des plans de décapages :
(2)

Code sql :

1
2
SELECT	*
FROM		V_PLAN_CSS_NEXPED

Voici la requête de sélection des caisses dans les plans de décapages:
(3)

Code sql :

1
2
3
4
5
6
SELECT	CDC.*
FROM		TJ_CAISSE_DECAP_CDC CDC
	INNER JOIN V_PLAN_CSS_NEXPED PDC
		ON CDC.PDC_ID = PDC.PDC_ID
	INNER JOIN TR_STATUTCAISSE_STT STT
		ON STT.STT_CSS_CODE = CDC.STT_CSS_CODE

Voici la requête de sélection des caisses associées:
(4)

Code sql :

1
2
3
4
5
6
7
8
SELECT	CSS.CSS_ID, CSS_NB_PCE, CSS_DATE_RCP, CSS_DATE_EXP, CSS.STT_CSS_CODE, STT_CSS_LIBELLE
FROM		T_CAISSES_CSS CSS
	INNER JOIN TJ_CAISSE_DECAP_CDC CDC
		ON CDC.CSS_ID = CSS.CSS_ID
	INNER JOIN V_PLAN_CSS_NEXPED PDC
		ON CDC.PDC_ID = PDC.PDC_ID
        INNER JOIN TR_STATUTCAISSE_STT STT
		ON CSS.STT_CSS_CODE = STT.STT_CSS_CODE

Cette dernière requête est très très longue. Elle retourne à peu près 4000 enregistrements en 1min10. J’ai cherché d’où pouvait provenir l’erreur et si j’enlève la dernière jointure
(5)

Code sql :

1
2
3
 
INNER JOIN TR_STATUTCAISSE_STT STT
	ON CSS.STT_CSS_CODE = STT.STT_CSS_CODE

La requête retourne les même 4000 lignes en 1 seconde.
Utilisant SQL Server 2005, j’ai exécuté la requête en activant le paramètre suivant :

Code sql :

SET STATISTICS IO ON

La requête (4) avec la jointure consomme 1003285 pages sur la table T_CAISSES_CSS
La requête (5) sans la jointure consomme 12132 pages.

J’ai essayé une base plus ancienne et possédant moins d’enregistrement.
Cette fois-ci les résultats sont :
6122 pages consommées sur la table T_CAISSES_CSS pour la requête (4)
6122 pages consommées sur la table T_CAISSES_CSS pour la requête (5)

Auriez-vous une idée des pistes vers lesquelles je pourrai me tourner pour résoudre ce gros problème ?
Merci d’avance pour votre aide et le temps que vous m’accorderez.

[ylarvor]

Auriez-vous une idée des pistes vers lesquelles je pourrai me tourner pour résoudre ce gros problème ?

Bonjour,

je vous ai lu. je n'ai pas de réponse directe. soit je n'ai pas le niveau, soit que votre description ne présente pas le creux du probléme.

Quelques précisions peuvent être utile quand même ?

Votre schéma MPD présente 2 tables nommées TR_STATUTCAISSE_STT, cela me parait étrange ? pas vous ?

Votre base est indexée au minimum ? Clé étrangères indexées.
Votre base est correctement maintenue ? Réindexation chaque semaine, Mise à jour des statistiques chaque semaine, Plan de maintenance en place.
Avez-vous penser à passer le tuning advisor de sql serveur 2005 sur votre requête de 1 minute pour déterminer si une amélioration significative était possible de manière automatique ?

[Menoto]

Bonsoir,

merci pour la réponse,

Envoyé par ylarvor

Votre schéma MPD présente 2 tables nommées TR_STATUTCAISSE_STT, cela me parait étrange ? pas vous ?

oui il s'agissait d'une erreur de copie-collé, j'ai corrigé le schéma. J'ai changé l'une des tables en TR_STATUTPLAN_SPL.

Envoyé par ylarvor

Votre base est indexée au minimum ? Clé étrangères indexées.

Oui, les index dont disposent chaque table sont les clés étrangères et les clés primaires en cluster.

Envoyé par ylarvor

Votre base est correctement maintenue ? Réindexation chaque semaine, Mise à jour des statistiques chaque semaine, Plan de maintenance en place.

Non. Je vais m'occuper de ce point. Je suppose que l'on peut programmer ces tâches de maintenance ?
Suite à votre message, j'ai mis à jour les statistiques, reconstruit et réorganisé les index de chaque table concernée par la requête.
Le nombre d'analyse pour la table T_CAISSE_CSS est passé de 7 à 2 et le nombre de lecture logiques est passé de 1003285 à 6810. Il semblerait donc que le problème venait de là. Il ne me reste plus qu'à mettre en place une maintenance automatique. Je vais regarder sur la MSDN.
Il faut donc je programme une mise à jour automatique des statistiques, une réorganisation et une reconstruction des index ? Y a-t-il autre chose ?

Envoyé par ylarvor

Avez-vous penser à passer le tuning advisor de sql serveur 2005 sur votre requête de 1 minute pour déterminer si une amélioration significative était possible de manière automatique ?

J'ai testé la requête avec l'outil tuning advisor, il me propose quelques conditions d'améliorations. Je vais étudier ça.

Merci pour votre aide.

[ylarvor]

il y a aussi la compression du log mais cela n'a pas d'influence sur les performances.

http://blog.developpez.com/index.php...&c=1&tb=1&pb=1

Je suis content d'avoir pu vous aidé.

A+

[SQLpro]

Pensez aussi à indexer CSS.CSS_DATE_EXP.

A +