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

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

Que donne le plan d'exécution ?

Essayez d'indexer

CONTACTS.ID_CONTACT
T.ORIGINAL_ID_CONTACT
et T.ID

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)

---