[ASE][T-SQL] Optimisation d'un update

[metheorn]

Bonjour,

Je n'arrive pas à obtenir un temps de traitement correct pour cette requete.
Elle s'effectue en 5 minutes pour 10.000 enregistrements traités, sur une table de 140.000 pour #TMP (index sur ID) et 900.000 pour CONTACTS (index sur ID_CONTACT).
Pour traiter les 140.000, il me faudra 1 heure ...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
UPDATE CONTACTS
SET
  ADR1 = T.NORMALISE_ADRESSE_1, 
  ADR2 = T.NORMALISE_ADRESSE_2, 
  ADR3 = T.NORMALISE_ADRESSE_3, 
  ADR4 = T.NORMALISE_ADRESSE_4, 
  CP = T.NORMALISE_CODE_POSTAL, 
  VILLE = T.NORMALISE_COMMUNE, 
  DATE_MAJ = @date_trt , 
  BAD_ADR	= 'O'   
FROM	#TMP T  
WHERE
  CONTACTS.ID_CONTACT = T.ORIGINAL_ID_CONTACT  
  AND T.ID BETWEEN @MinId AND @MinIdUp

exemple : @MinId = 1 et @MinIdUp=10000

Merci de votre aide

[fadace]

Que donne le plan d'exécution ?

Essayez d'indexer

CONTACTS.ID_CONTACT
T.ORIGINAL_ID_CONTACT
et T.ID

[metheorn]

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
SQL Server Message:  (sev 0, MsgNo 6248, lineNo 1)
SQL Server Message: QUERY PLAN FOR STATEMENT 1 (at line 1). (sev 0, MsgNo 6289, lineNo 1)
SQL Server Message:  (sev 0, MsgNo 6248, lineNo 1)
SQL Server Message:  (sev 0, MsgNo 6248, lineNo 1)
SQL Server Message:     STEP 1 (sev 0, MsgNo 6201, lineNo 1)
SQL Server Message:         The type of query IS UPDATE. (sev 0, MsgNo 6203, lineNo 1)
SQL Server Message:         The UPDATE mode IS deferred. (sev 0, MsgNo 6205, lineNo 1)
SQL Server Message:  (sev 0, MsgNo 6248, lineNo 1)
SQL Server Message:         FROM TABLE (sev 0, MsgNo 6215, lineNo 1)
SQL Server Message:             #TMP (sev 0, MsgNo 6217, lineNo 1)
SQL Server Message:             T (sev 0, MsgNo 6217, lineNo 1)
SQL Server Message:         Nested iteration. (sev 0, MsgNo 6219, lineNo 1)
SQL Server Message:         USING Clustered INDEX. (sev 0, MsgNo 6224, lineNo 1)
SQL Server Message:         INDEX : IND_TMP (sev 0, MsgNo 6225, lineNo 1)
SQL Server Message:         Forward scan. (sev 0, MsgNo 6276, lineNo 1)
SQL Server Message:         Positioning BY KEY. (sev 0, MsgNo 6281, lineNo 1)
SQL Server Message:         KEYS are: (sev 0, MsgNo 6287, lineNo 1)
SQL Server Message:             ID  ASC (sev 0, MsgNo 6288, lineNo 1)
SQL Server Message:         USING I/O Size 16 Kbytes FOR DATA pages. (sev 0, MsgNo 10240, lineNo 1)
SQL Server Message:         WITH LRU Buffer Replacement Strategy FOR DATA pages. (sev 0, MsgNo 10239, lineNo 1)
SQL Server Message:  (sev 0, MsgNo 6248, lineNo 1)
SQL Server Message:         FROM TABLE (sev 0, MsgNo 6215, lineNo 1)
SQL Server Message:             CONTACTS (sev 0, MsgNo 6217, lineNo 1)
SQL Server Message:         EXISTS TABLE : nested iteration. (sev 0, MsgNo 6220, lineNo 1)
SQL Server Message:         USING Clustered INDEX. (sev 0, MsgNo 6224, lineNo 1)
SQL Server Message:         INDEX : I_PK_CON_ID (sev 0, MsgNo 6225, lineNo 1)
SQL Server Message:         Forward scan. (sev 0, MsgNo 6276, lineNo 1)
SQL Server Message:         Positioning BY KEY. (sev 0, MsgNo 6281, lineNo 1)
SQL Server Message:         KEYS are: (sev 0, MsgNo 6287, lineNo 1)
SQL Server Message:             ID_CONTACT  ASC (sev 0, MsgNo 6288, lineNo 1)
SQL Server Message:         USING I/O Size 2 Kbytes FOR DATA pages. (sev 0, MsgNo 10240, lineNo 1)