Discussion :

[SuperWaza]

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 :

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

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

[K2R400]

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 :

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

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

[pdz74]

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 :

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

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.

[SuperWaza]

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

[K2R400]

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

Code :

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

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

[SuperWaza]

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