[ASE][T-SQL] Requete qui n'aboutit pas

[metheorn]

Un update de 20 604 lignes depuis une table de travail comportant le même nombre de lignes n'aboutit pas !...
Merci de votre aide

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
SELECT INDCLI_ID, INDCLI_PRENOM_NOM, INDCLI_ADRESSE1,  INDCLI_CODE_POSTAL
INTO #WA_AF
FROM WA_INDIVIDU_CLIENT
WHERE  INDCLI_PRENOM_NOM = 'AIR FRANCE HOST'
-- 20 604  lignes en 2s / 99% de tempdb disponible
 
 CREATE clustered INDEX idx_dedoub ON #WA_AF(INDCLI_ADRESSE1,  INDCLI_CODE_POSTAL, INDCLI_PRENOM_NOM, INDCLI_ID)
-- 1s / 99% de tempdb disponible
 
 UPDATE
     WA_INDIVIDU_CLIENT
SET
    INDCLI_FLAG =  1
WHERE
    INDCLI_FLAG = 0
    AND  INDCLI_CLI_AH_ID = 0
    AND INDCLI_PRENOM_NOM = 'AIR FRANCE  HOST'
    AND EXISTS (SELECT INDCLI_ID FROM #WA_AF  T2
                         WHERE  
                             WA_INDIVIDU_CLIENT.INDCLI_PRENOM_NOM =  T2.INDCLI_PRENOM_NOM
                             AND WA_INDIVIDU_CLIENT.INDCLI_ADRESSE1 =  T2.INDCLI_ADRESSE1
                             AND WA_INDIVIDU_CLIENT.INDCLI_CODE_POSTAL =  T2.INDCLI_CODE_POSTAL
                             AND isnull(T2.INDCLI_PRENOM_NOM,' ') <> '  '
                             AND isnull(T2.INDCLI_ADRESSE1,' ') <> '  '
                             AND isnull(T2.INDCLI_CODE_POSTAL,' ') <> '  '
                             AND (WA_INDIVIDU_CLIENT.INDCLI_CLI_AH_ID =  0
                                 OR WA_INDIVIDU_CLIENT.INDCLI_CLI_AH_ID > T2.INDCLI_ID)  
                             AND WA_INDIVIDU_CLIENT.INDCLI_ID <>  T2.INDCLI_ID
                )
-- 20 604 lignes en 12s
 
 UPDATE
     WA_INDIVIDU_CLIENT
SET
    INDCLI_CLI_AH_ID = (SELECT  min(T2.INDCLI_ID) FROM #WA_AF  T2
                         WHERE  
                             WA_INDIVIDU_CLIENT.INDCLI_PRENOM_NOM =  T2.INDCLI_PRENOM_NOM
                             AND WA_INDIVIDU_CLIENT.INDCLI_ADRESSE1 =  T2.INDCLI_ADRESSE1
                             AND WA_INDIVIDU_CLIENT.INDCLI_CODE_POSTAL =  T2.INDCLI_CODE_POSTAL
                             AND WA_INDIVIDU_CLIENT.INDCLI_ID <> T2.INDCLI_ID),
     INDCLI_CRITERE = 1
WHERE
    INDCLI_FLAG =  1
    AND INDCLI_CLI_AH_ID = 0
    AND  INDCLI_PRENOM_NOM = 'AIR FRANCE HOST'
-- la  requête n'aboutit pas (aucun IO physique ...)

[fadace]

Qu'en est-il du plan d'exécution (showplan) ? Et des verrous (sp_lock) ? Et du statut de la connexion (sp_who)? Ne voyez-vous effectivement rien bouger ?

[metheorn]

Pas de verrou, et un sp_who normal.
En fait, les IO augmentent à environ 10.000 pendant la 1ère minute puis s'arrêtent !
Je récupère le showplan...

[metheorn]

Le plan d'exécution :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
 
QUERY PLAN FOR STATEMENT 1 (at line 1). 
 
    STEP 1 
        The type of query IS UPDATE. 
        The UPDATE mode IS direct. 
 
 
        FROM TABLE 
            WA_INDIVIDU_CLIENT 
        Nested iteration. 
        TABLE Scan. 
        Forward scan. 
        Positioning at start of TABLE. 
 
 
        Run subquery 1 (at nesting level 1). 
        USING I/O Size 16 Kbytes FOR DATA pages. 
        WITH MRU Buffer Replacement Strategy FOR DATA pages. 
        TO TABLE 
            WA_INDIVIDU_CLIENT 
        USING I/O Size 2 Kbytes FOR DATA pages. 
    STEP 1 
 
 
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1. 
 
 
  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 AND at line 4). 
 
 
    Correlated Subquery. 
    Subquery under an EXPRESSION predicate. 
 
 
 
 
    STEP 1 
        The type of query IS SELECT. 
        Evaluate Ungrouped MINIMUM AGGREGATE. 
 
 
        FROM TABLE 
            #WA_AF 
            T2 
        Nested iteration. 
        TABLE Scan. 
        Forward scan. 
        Positioning at start of TABLE. 
        USING I/O Size 16 Kbytes FOR DATA pages. 
        WITH LRU Buffer Replacement Strategy FOR DATA pages. 
 
 
  END OF QUERY PLAN FOR SUBQUERY 1.

Les tables utilisées :

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
68   fidelisa Ex_intent    ANAFID   WA_INDIVIDU_CL           0 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502984 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502985 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502986 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502987 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502988 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502989 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502990 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5502991 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503003 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503004 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503005 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503006 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503007 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503008 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503009 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503010 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503011 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503012 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503013 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503014 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503015 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503016 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503017 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503018 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503019 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503020 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503021 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503022 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503023 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503032 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503033 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503034 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503035 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503036 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503037 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503038 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503039 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503040 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503041 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503042 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503048 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503049 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503050 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503051 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503052 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503053 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503054 0    NULL  
68   fidelisa Ex_page      ANAFID   WA_INDIVIDU_CL     5503055 0    NULL  
68   fidelisa Ex_table     tempdb   #WA_AF_______0           0 0    NULL  
68   fidelisa Update_page  ANAFID   WA_INDIVIDU_CL     5503042 0    NULL

A quoi correspondent toutes ces Ex_page ?

[remi4444]

Quoi qu'il en soit, un petit index sur la colonne INDCLI_PRENOM_NOM, ne devrait pas faire de mal... il faudra peut etre le forcer dans le plan d'execution car avec les tables temporaires, on ne sais jamais comment l'optimiseur réagi...

Code :

1
2
3
4
5
6
7
8
9
10
 
CREATE INDEX #IDX_WA_AF on #WA_AF ( INDCLI_PRENOM_NOM )
 
 
.....
 
SET
  INDCLI_CLI_AH_ID = (SELECT  min(T2.INDCLI_ID) FROM #WA_AF (index #IDX_WA_AF ) T2
 
.....