Optimisation code Sql

Version imprimable

Cette requête donne de mauvais temps de réponses ... comment optimiser celle-ci ? La vue MSIS.VW_MSIS0012 est une sélection de n colonnes de la table MSIS.SERVICE ... es-ce la vue qui donne ces mauvaises perf ?

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
 
SELECT T5.NS_SERVICE_ID, 
       CASE WHEN T5.NS_FRATRIE_ID IS NULL THEN 0 
        ELSE T5.NS_FRATRIE_ID 
       END AS FRATRIE_ID, 
       COUNT (*) AS NB_PEC
 
FROM (
 
SELECT T1.NS_SERVICE_ID, (SELECT T2.NS_FRATRIE_ID
                          FROM CIOC.FRATRIE T2
                          WHERE T2.NS_JEUNE_ID = T1.NS_JEUNE_ID 
                                AND T2.DT_DEBUT <= CURRENT_DATE 
                                AND (T2.DT_FIN >= CURRENT_DATE OR T2.DT_FIN IS NULL))
 
FROM CIOC.PEC T1, MSIS.SERVICE, MSIS.VW_MSIS0012 T4
 
WHERE T1.NS_SERVICE_ID = T4.NS_SERVICE_ID 
      AND MSIS.SERVICE.NS_SERVICE_ID_LNK = T4.NS_SERVICE_ID_LNK 
      AND MSIS.SERVICE.NS_SERVICE_ID = :AI_NS_SERVICE_ID AND T1.SW_COMPTE = 'O' 
      AND T1.DT_DEB_MANDAT <= :AI_DT_DEBUT 
      AND T1.DT_FIN_MANDAT > :AI_DT_FIN 
      AND T4.DT_DEBUT <= :AI_DT_DEBUT 
      AND (T4.DT_FIN >= :AI_DT_FIN OR T4.DT_FIN IS NULL) 
      AND MSIS.SERVICE.DT_DEBUT <= :AI_DT_DEBUT 
      AND (MSIS.SERVICE.DT_FIN >= :AI_DT_FIN OR MSIS.SERVICE.DT_FIN IS NULL)
 
) T5
 
GROUP BY T5.NS_SERVICE_ID, T5.NS_FRATRIE_ID

Essaye ceci et note les BETWEEN et le IFNULL pour remplacer ton CASE.
M'enfin, les performances reposent surtout sur l'indexation de ces tables.
Par exemple, il serait bon d'avoir des EVI (Encoded Vector Index) sur les dates de toutes tes tables, sans oublier des index sur les colonnes de jointure :

CREATE ENCODED VECTOR INDEX CIOC.FRATRIEix1 on CIOC.FRATRIE(DT_DEB_MANDAT) with 30000 Distinct Values;
CREATE ENCODED VECTOR INDEX CIOC.FRATRIEix2 on CIOC.FRATRIE(DT_FIN_MANDAT) with 30000 Distinct Values;
CREATE INDEX CIOC.FRATRIEix3 ON CIOC.FRATRIE (NS_JEUNE_ID)
...
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 With t1 as (SELECT NS_SERVICE_ID, NS_JEUNE_ID FROM CIOC.PEC Where DT_DEB_MANDAT <= :AI_DT_DEBUT AND DT_FIN_MANDAT > :AI_DT_FIN AND SW_COMPTE = 'O'), t2 as (SELECT T1.NS_SERVICE_ID, IFNULL(T2.NS_FRATRIE_ID, 0) FRATRIE_ID FROM CIOC.FRATRIE T2, T1 WHERE T2.NS_JEUNE_ID = T1.NS_JEUNE_ID AND (CURRENT_DATE BETWEEN T2.DT_DEBUT AND T2.DT_FIN OR T2.DT_FIN IS NULL)), t3 as (select NS_SERVICE_ID_LNK, NS_SERVICE_ID FROM MSIS.SERVICE where NS_SERVICE_ID = :AI_NS_SERVICE_ID And DT_DEBUT BETWEEN :AI_DT_DEBUT AND :AI_DT_FIN OR DT_FIN IS NULL), t4 as (Select NS_SERVICE_ID, NS_SERVICE_ID_LNK Where DT_DEBUT BETWEEN :AI_DT_DEBUT AND :AI_DT_FIN or DT_FIN IS NULL) Select t2.NS_SERVICE_ID, T2.FRATRIE_ID, COUNT (*) AS NB_PEC from t2, t3, t4 WHERE T2.NS_SERVICE_ID = T4.NS_SERVICE_ID AND T3.NS_SERVICE_ID_LNK = T4.NS_SERVICE_ID_LNK GROUP BY T2.NS_SERVICE_ID, T2.FRATRIE_ID

Pourquoi compliquer les requêtes en se servant de ss-requête, de nested tables, ..., lorsqu'on peut s'en passer. Sauf erreur de ma part, ta requête peut s'écrire :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 SELECT T1.NS_SERVICE_ID, T2.NS_FRATRIE_ID, COUNT (*) AS NB_PEC FROM CIOC.PEC T1, MSIS.SERVICE, MSIS.VW_MSIS0012 T4, CIOC.FRATRIE T2 WHERE T1.NS_SERVICE_ID = T4.NS_SERVICE_ID AND MSIS.SERVICE.NS_SERVICE_ID_LNK = T4.NS_SERVICE_ID_LNK AND MSIS.SERVICE.NS_SERVICE_ID = :AI_NS_SERVICE_ID AND T1.SW_COMPTE = 'O' AND T1.DT_DEB_MANDAT <= :AI_DT_DEBUT AND T1.DT_FIN_MANDAT > :AI_DT_FIN AND T4.DT_DEBUT <= :AI_DT_DEBUT AND (T4.DT_FIN >= :AI_DT_FIN OR T4.DT_FIN IS NULL) AND MSIS.SERVICE.DT_DEBUT <= :AI_DT_DEBUT AND (MSIS.SERVICE.DT_FIN >= :AI_DT_FIN OR MSIS.SERVICE.DT_FIN IS NULL) AND T2.NS_JEUNE_ID = T1.NS_JEUNE_ID AND T2.DT_DEBUT <= CURRENT_DATE AND (T2.DT_FIN >= CURRENT_DATE OR T2.DT_FIN IS NULL) GROUP BY T1.NS_SERVICE_ID, T2.NS_FRATRIE_ID
Je ne dis pas que tes problèmes de temps de réponse viennent de là, mais en général, plus on complique une requête, plus on a des chances que DB2 se plante quant aux choix des chemins d'accès.

Si tes statistiques sont correctes et que les bons index existent, la logique voudrait que DB2 accède :
1/ Table MSIS_SERVICE avec un index sur NS_SERVICE_ID
2/ Table T4 avec un index sur NS_SERVICE_ID_LNK
3/ Table T1 avec un index sur NS_SERVICE_ID
4/ Table T2 avec un index sur NS_JEUNE_ID

Si les dates sont présentes dans les index en colonnes supplémentaires, les temps de réponses n'en seront que meilleurs, car tu seras la plupart du temps en indexonly, cad sans accès aux datas ce qui est tout bénef.

Bonne utilisation et bonne journée.

27/07/2009, 12h26
SuperWaza

Ces deux propositions donnent des résultats différents par rapport à la requête principale :(

Me suis trompé sur les dates, ce ne sont pas des BETWEEN.

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
WITH 
t1 AS (SELECT NS_SERVICE_ID, NS_JEUNE_ID
         FROM CIOC.PEC
	WHERE DT_DEB_MANDAT <= :AI_DT_DEBUT 
        AND DT_FIN_MANDAT > :AI_DT_FIN 
	AND SW_COMPTE = 'O'),
 
t2 AS (SELECT T1.NS_SERVICE_ID, IFNULL(T2.NS_FRATRIE_ID, 0) FRATRIE_ID
	FROM CIOC.FRATRIE x2, T1
	WHERE x2.NS_JEUNE_ID = T1.NS_JEUNE_ID
          AND x2.DT_DEBUT <= CURRENT_DATE 
          AND (x2.DT_FIN >= CURRENT_DATE OR x2.DT_FIN IS NULL)),
 
t3 AS (SELECT NS_SERVICE_ID_LNK, NS_SERVICE_ID
	FROM  MSIS.SERVICE
	WHERE NS_SERVICE_ID = :AI_NS_SERVICE_ID
          AND DT_DEBUT <= :AI_DT_DEBUT 
          AND (DT_FIN >= :AI_DT_FIN OR DT_FIN IS NULL)),
 
 
t4 AS (SELECT NS_SERVICE_ID, NS_SERVICE_ID_LNK 
	FROM MSIS.VW_MSIS0012
	WHERE DT_DEBUT <= :AI_DT_DEBUT 
	  AND (DT_FIN >= :AI_DT_FIN OR DT_FIN IS NULL)) 
 
 
SELECT t2.NS_SERVICE_ID, T2.FRATRIE_ID, COUNT (*) AS NB_PEC
FROM t2, t3, t4
WHERE T2.NS_SERVICE_ID     = T4.NS_SERVICE_ID 
  AND T3.NS_SERVICE_ID_LNK = T4.NS_SERVICE_ID_LNK 
GROUP BY T2.NS_SERVICE_ID, T2.FRATRIE_ID

28/07/2009, 15h31
SuperWaza

:( le résultat est toujours vide ... ai essayé tellement de variantes :calim2: