Requêtes imbriquées & Isolation Level

**Glouferu** · 08/08/2011, 15h47

Bonjour à tous,

J'ai un problème avec une requête SQL imbriquée qui ne s’exécute jamais !
Après avoir étudié le plan d’exécution en détails, rajouté 2 3 index et mis à jour les Statistiques, mon problème de blocage est toujours présent !

Comme la requête provient d'un univers Business Object, je n'ai pas forcément étudié le SQL au premier abord.
Néanmoins, il intéressant de voir que cette requête possède une sous-requête :

Requête principal (qui fonctionne parfaitement de manière indépendante) :

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.

(423221 row(s) affected)
Table 'SCHEDULE'. Scan count 0, logical reads 1623507, physical reads 0,
Table 'PARTNER'. Scan count 0, logical reads 1082338, physical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
Table 'DTMPFO_IMPRESSIONS'. Scan count 4, logical reads 41732, physical reads 0,
Table 'POLICY_STATUS_ACTUAL'. Scan count 1, logical reads 7476, physical reads 0,
Table 'POLICY_STATUS'. Scan count 2, logical reads 4, physical reads 0,
Table 'POLICY'. Scan count 1, logical reads 72329, physical reads 0,
Table 'CALENDAR'. Scan count 1, logical reads 44, physical reads 0,

SQL Server Execution Times:
CPU time = 31719 ms, elapsed time = 43090 ms.

Requête secondaire qui s’exécute aussi parfaitement :

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(6001 row(s) affected)
Table 'SCHEDULE'. Scan count 0, logical reads 18387, physical reads 0,
Table 'POLICY'. Scan count 0, logical reads 18387, physical reads 0,
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0,
Table 'CALENDAR'. Scan count 1, logical reads 44, physical reads 0,
Table 'DTMPFO_IMPRESSIONS'. Scan count 1, logical reads 234279, physical reads 0,

SQL Server Execution Times:
CPU time = 1375 ms, elapsed time = 1437 ms.

D'après mon expertise

, la requête imbriquée devrait fonctionner sans problème ! Je ne comprends pas d’où provient le problème !!!???

Quand j’exécute cette dernière, je ne constate pas de temps d'attente suspect ... Je me demande si ce n'est pas un problème d'isolation, pourtant je n'ai jamais eu de problème à ce niveau la !

Qu'en pensez-vous ?

Par avance,
Merci de vos réponses.

P.S.: Ce n'est pas la première fois que je constate des problèmes avec des sous-requêtes dont le SQL est généré par Deski. Il est certainement possible de revoir la logique de la requête (univers), mais cette requête devrait sortir ...

Config :
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)
Enterprise Edition (64-bit) on Windows NT 5.2 <X64>
(Build 3790: Service Pack 2).

Ressource Gouvernor configuré !

**aieeeuuuuu** · 08/08/2011, 17h56

Bonjour,

peut-on voir la requête en question ?

Avez regardé s'il ne manquait pas d'index pour cette requête ?

**Glouferu** · 08/08/2011, 18h29

Bonjour aieeeuuuuu,

Je suis entrain d'étudier un peu plus le plan d'execution, je constate beaucoup de NESTED LOOPS en fin de requête. C'est peut-être une piste.
Je cherche aussi à placer des HINTS dans ma requête voir si je peux améliorer les choses ...

--> Le plan d’exécution de la requête se trouve ici !

La requête SQL fait un peu peur ... :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
SELECT DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_TYPE,CASE
                                                     WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                     ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                 END,CASE
                                                         WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                         ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                     END,CASE
                                                             WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                             ELSE SCHEDULE.CD_SCHEDULE
                                                         END,SUM(DTMPFO.dbo.DTMPFO_IMPRESSIONS.FG_RENEW),PARTNER.CD_PARTNER,PARTNER.LB_PARTNER
FROM CALENDAR
RIGHT OUTER JOIN DTMPFO.dbo.DTMPFO_IMPRESSIONS ON (CALENDAR.LB_DAY=CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME))
LEFT OUTER JOIN(SELECT [ITEMCODE],[GROUPCODE],[DESCRIPTION]
                FROM dbo03.[ODS].[dbo].[ODS_ENS_DECODE]
                WHERE [GROUPCODE]='reqstat') DECODE_REQSTAT ON (upper(DECODE_REQSTAT.ITEMCODE)=upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.STATUS))
LEFT OUTER JOIN POLICY ON (DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY)
LEFT OUTER JOIN PARTNER ON (POLICY.CD_PARTNER=PARTNER.CD_PARTNER)
LEFT OUTER JOIN POLICY_STATUS_ACTUAL ON (POLICY_STATUS_ACTUAL.CD_POLICY=POLICY.CD_POLICY)
LEFT OUTER JOIN POLICY_STATUS ON (POLICY_STATUS.CD_POLICY_STATUS=POLICY_STATUS_ACTUAL.CD_POLICY_STATUS)
LEFT OUTER JOIN SCHEDULE ON (SCHEDULE.CD_SCHEDULE=DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE)
WHERE (DECODE_REQSTAT.DESCRIPTION IN('Imprimé','Terminé') AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_TYPE IN('CN1','RN1','RN2','RN3') AND CASE
                                                                                                                                            WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                                                                                                            ELSE SCHEDULE.CD_SCHEDULE
                                                                                                                                        END IN(SELECT CASE
                                                                                                                                                          WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                                                                                                                          ELSE SCHEDULE.CD_SCHEDULE
                                                                                                                                                      END
                                                                                                                                               FROM CALENDAR
                                                                                                                                               RIGHT OUTER JOIN DTMPFO.dbo.DTMPFO_IMPRESSIONS ON (CALENDAR.LB_DAY=CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME))
                                                                                                                                               LEFT OUTER JOIN(SELECT [ITEMCODE],[GROUPCODE],[DESCRIPTION]
                                                                                                                                                               FROM dbo03.[ODS].[dbo].[ODS_ENS_DECODE]
                                                                                                                                                               WHERE [GROUPCODE]='reqstat') DECODE_REQSTAT ON (upper(DECODE_REQSTAT.ITEMCODE)=upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.STATUS))
                                                                                                                                               LEFT OUTER JOIN POLICY ON (DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY)
                                                                                                                                               LEFT OUTER JOIN SCHEDULE ON (SCHEDULE.CD_SCHEDULE=DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE)
                                                                                                                                               WHERE ((CASE
                                                                                                                                                           WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                                                                                                                           ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                                                                                                                       END IN('47_CNA_A4','47_RNA_A4','47_RNB_A4') OR CASE
                                                                                                                                                                                                          WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                                                                                                                                                                          ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                                                                                                                                                                      END IN('47D_CNA_A4','47D_RNA_A4','47D_RNB_A4','47V_CNA_A4','47V_RNA_A4','47V_RNB_A4') AND CASE
                                                                                                                                                                                                                                                                                                    WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                    ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                END NOT BETWEEN '04/01/2010 00:0:0' AND '04/13/2010 00:0:0') AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_RENEW_THEO BETWEEN '2011-07-01' AND '2011-07-31' AND DECODE_REQSTAT.DESCRIPTION IN('Imprimé','Terminé'))) AND POLICY_STATUS.CD_POLICY_STATUS!='Z' AND CASE
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        END>='2011-02-01')
GROUP BY DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_TYPE,CASE
                                                       WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME='' THEN NULL
                                                       ELSE upper(DTMPFO.dbo.DTMPFO_IMPRESSIONS.LETTER_NAME)
                                                   END,CASE
                                                           WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL THEN CAST(CONVERT(VARCHAR(10),DTMPFO.dbo.DTMPFO_IMPRESSIONS.DT_REQUEST,112) AS DATETIME)
                                                           ELSE CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME)
                                                       END,CASE
                                                               WHEN DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_SCHEDULE IS NULL AND DTMPFO.dbo.DTMPFO_IMPRESSIONS.CD_POLICY=POLICY.CD_POLICY THEN POLICY.CD_SCHEDULE
                                                               ELSE SCHEDULE.CD_SCHEDULE
                                                           END,PARTNER.CD_PARTNER,PARTNER.LB_PARTNER
HAVING (SUM(DTMPFO.dbo.DTMPFO_IMPRESSIONS.FG_RENEW)=1)

**aieeeuuuuu** · 08/08/2011, 19h21

Je ne pense pas que poser des hint soit une bonne idée, et cela risque probablement d’être pire que mieux !

je n'ai pas approfondi sur la requête, mais je pense que vous devriez commencer par (ce ne sont que des pistes...) :

- Vérifier que votre critères de recherche sont sargables, surtout en ce qui concerne les tables DTMPFO_IMPRESSIONS, POLICY, POLICY_STATUS et POLICY_STATUS_ACTUAL. rien que cette partie represente pres de 90% du cout de la requete. peut etre pouvez vous envisager des vues indexees...
- qu'il ne manque pas d'index
- remplacer vos IN par des jointures internes
- vérifier le type de vos colonnes. Vous effectuez un grand nombre de transtypage, y compris dans des jointures !
- il en va de même pour les uppers en grand nombre (dans des jointures aussi) qui pourraient surement être évités

quel est le but de ceci :

Code SQL :

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

1
2
 
WHEN CAST(CONVERT(VARCHAR(10),CALENDAR.LB_DAY,112) AS DATETIME) IS NULL

**Glouferu** · 09/08/2011, 11h08

Bonjour,

Ce matin, la requête met 25 secondes et s’exécute normalement ! :

Je n'ai strictement rien fait... les plans de maintenance s’exécutent le dimanche ! Ma base a l'option "Auto Update Statistics" !
Il n'y avait aucune charge sur le serveur hier après midi...

Je ne comprends vraiment pas comment c'est possible !?

As-tu une idée Aiiieuuu ?

Merci

**aieeeuuuuu** · 09/08/2011, 11h23

Bonjour

Avez vous vérifié si le plan d’exécution avait changé ?

Requêtes imbriquées & Isolation Level

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager