[SYBASE] optimisation requete UPDATE

Bonjour,

Est-il possible d'améliorer la requête ci-dessous sachant que les deux tables ont un index sur ID_CONTACT

@MinId et @MinIdUp sont des bornes de 3000 enregistrements afin de ne pas remplir les logs.

Merci de votre aide

Code:

---

1 2 3 4 5 6 7

update CONTACTS set DAT_DER_CURE=T2.DATE_DEB, SIT_DER_CURE=T2.CODE_SITE, HOT_DER_SEJ=T2.HEBERGT from #LAST_CURE_2 T2 where CONTACTS.ID_CONTACT=T2.ID_CONTACT AND T2.ID_CONTACT between @MinId and @MinIdUp

---

Table CONTACTS : 800.000 enregistrements
Table #LAST_CURE : 300.000 enregistrements

Je pense que non.
Pour en etre sur, pouvez-vous nous retourner le showplan ?

Soit

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

set showplan on go set noexec on go update CONTACTS set DAT_DER_CURE=T2.DATE_DEB, SIT_DER_CURE=T2.CODE_SITE, HOT_DER_SEJ=T2.HEBERGT from #LAST_CURE_2 T2 where CONTACTS.ID_CONTACT=T2.ID_CONTACT AND T2.ID_CONTACT between @MinId and @MinIdUp go set noexec off go set showplan off go

---

C'est le showplan d'une autre requete du même style.

Code:

---

1 2 3 4 5

UPDATE CONTACTS SET RECENCE_THA = RECENCE FROM #TMP_RECENCE TMP WHERE TMP.ID_CONTACT = CONTACTS.ID_CONTACT AND TMP.ID_CONTACT between 110744455 and 110754455

---

100 mises à jour en 5 secondes. Je ne trouve pas cela très rapide.

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

QUERY PLAN FOR STATEMENT 1 (at line 1).   STEP 1 The type of query is UPDATE. The update mode is deferred.   FROM TABLE #TMP_RECENCE TMP Nested iteration. Using Clustered Index. Index : IND_TMP_RECENCE Forward scan. Positioning by key. Keys are: ID_CONTACT ASC Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages.   FROM TABLE CONTACTS EXISTS TABLE : nested iteration. Using Clustered Index. Index : I_PK_CON_ID Forward scan. Positioning by key. Keys are: ID_CONTACT ASC Using I/O Size 2 Kbytes for data pages. With LRU Buffer Replacement Strategy for data pages. TO TABLE CONTACTS Using I/O Size 2 Kbytes for data pages.   Server Message: Number 3630, Severity 10 Server 'SYBDEV', Line 1: Total estimated I/O cost for statement 1 (at line 1): 88082.

---

Le probleme, c'est l'update en mode defered venant de la clause from...

En terme d'index, on ne peut pas faire bcp mieux que ce qu'il y a a present (2 index cluster)...

Vous serait-il posible d'executer un sp_sysmon pendant un update afin de voir ou cela peche ?

Code:

---

1 2 3 4 5 6 7 8 9

exec sp_sysmon begin_sample update CONTACTS set DAT_DER_CURE=T2.DATE_DEB, SIT_DER_CURE=T2.CODE_SITE, HOT_DER_SEJ=T2.HEBERGT from #LAST_CURE_2 T2 where CONTACTS.ID_CONTACT=T2.ID_CONTACT AND T2.ID_CONTACT between @MinId and @MinIdUp exec sp_sysmon end_sample

---

selon voter serveur, l'output peut-etre un peu trop verbeux, alors... j'attends juste les morceaux choisis :wink:

Quant aux 5 secondes, tout est relatif (il peut par exemple s'agir d'un verrou bloquant)... votre update execute quand meme plus de 88000 acces a des pages de 2K :!: ...

Le problème est que je ne suis pas seul sur le serveur. le sp_sysmon sera pollué par les autres utilisateurs.

Concernant le temps d'exécution, ce n'est pas 5 secondes mais plutôt 5-10 minutes pour 4500 enregistrements :? .

Je vais reformuler le contexte, cela pourra peut être ouvrir une autre piste.

Objectif :
Mettre à jour un champ de la table CONTACTS (champ non indexé) par une valeur de la table #TMP_RECENCE.
La jointure se fait sur l'ID_CONTACT

Contraintes :
Afin de ne pas bloquer le serveur en remplissant les logs, on nous a demandé de splitter les requêtes.
Nous faisons donc plusieurs requetes selon un interval sur ID_CONTACT.

2 tables :
Table CONTACTS : 800.000 enregistrements (ID_CONTACT unique)
Table #LAST_CURE : 300.000 enregistrements (ID_CONTACT unique)

Requete :

Code:

---

1 2 3 4 5

UPDATE CONTACTS SET RECENCE_THA = RECENCE FROM #TMP_RECENCE TMP WHERE TMP.ID_CONTACT = CONTACTS.ID_CONTACT AND TMP.ID_CONTACT between 110744455 and 110754455

---

Cette requête est-elle le seul moyen de mettre à jour la table CONTACTS ?
Merci

Il y a aussi la possibilite peu elegante de faire des UPDATES successifs en utilisant le

Code:

---

set rowcount n

---

afin de ne pas saturer le log... mais ca depend de l'update implique.

En ce qui concerne la "POLLUTION" et sp_sysmon, c'est justement elle qui est interessante : si les autres utilisateurs posent des verrous sur ladite table, cela se verra.

Non, je suis le seul à attaquer ces tables.

Voici dans tous les cas quelques résultats du sp_sysmon. SI tu as besoin de certaine partie plus particulièrement, je pourrais également les poster.

Dans tous les cas, merci de ton aide sur ce sujet.

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

Kernel Utilization ------------------   Your Runnable Process Search Count is set to 300 and I/O Polling Process Count is set to 10   Engine Busy Utilization CPU Busy I/O Busy Idle ------------------------ -------- -------- -------- Engine 0 0.9 % 33.7 % 65.4 % Engine 1 1.1 % 68.4 % 30.5 % Engine 2 1.3 % 21.7 % 77.0 % ------------------------ -------- -------- -------- Summary Total 3.3 % 123.8 % 172.9 % Average 1.1 % 41.3 % 57.6 %   CPU Yields by Engine per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Engine 0 32.7 134.2 30321 37.4 % Engine 1 16.1 66.1 14938 18.4 % Engine 2 38.6 158.4 35794 44.2 % ------------------------- ------------ ------------ ---------- Total CPU Yields 87.4 358.6 81053   Network Checks Non-Blocking 58506.5 239980.3 54235544 99.9 % Blocking 87.4 358.6 81051 0.1 % ------------------------- ------------ ------------ ---------- Total Network I/O Checks 58594.0 240338.9 54316595 Avg Net I/Os per Check n/a n/a 0.00034 n/a   Disk I/O Checks Total Disk I/O Checks 58637.1 240515.8 54356581 n/a Checks Returning I/O 56518.4 231825.3 52392513 96.4 % Avg Disk I/Os Returned n/a n/a 0.00108 n/a   Tuning Recommendations for Kernel Utilization --------------------------------------------- - Consider decreasing the 'runnable process search count' configuration parameter if you require the CPU's on the machine to be used for other applications.

---

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

Task Management per sec per xact count % of total --------------------------- ------------ ------------ ---------- ----------   Connections Opened 0.0 0.1 12 n/a   Task Context Switches by Engine Engine 0 8.9 36.4 8237 16.3 % Engine 1 17.6 72.1 16293 32.3 % Engine 2 27.9 114.5 25873 51.3 % ------------------------- ------------ ------------ ---------- Total Task Switches: 54.4 223.0 50403   Task Context Switches Due To: Voluntary Yields 6.0 24.5 5533 11.0 % Cache Search Misses 20.9 85.7 19368 38.4 % System Disk Writes 0.7 2.9 650 1.3 % I/O Pacing 1.8 7.2 1627 3.2 % Logical Lock Contention 0.0 0.0 0 0.0 % Address Lock Contention 0.0 0.0 0 0.0 % Latch Contention 0.0 0.0 0 0.0 % Log Semaphore Contention 0.0 0.0 0 0.0 % PLC Lock Contention 0.0 0.0 1 0.0 % Group Commit Sleeps 0.1 0.2 53 0.1 % Last Log Page Writes 0.8 3.1 699 1.4 % Modify Conflicts 0.8 3.2 727 1.4 % I/O Device Contention 0.0 0.0 0 0.0 % Network Packet Received 7.7 31.7 7172 14.2 % Network Packet Sent 3.6 14.8 3340 6.6 % Other Causes 12.1 49.7 11233 22.3 %

---

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

Transaction Profile -------------------   Transaction Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Committed Xacts 0.2 n/a 226 n/a   Transaction Detail per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Inserts APL Heap Table 16.6 68.1 15396 38.6 % APL Clustered Table 26.4 108.3 24481 61.4 % Data Only Lock Table 0.0 0.1 19 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total Rows Inserted 43.0 176.5 39896 80.8 %   Updates APL Deferred 5.0 20.3 4599 99.9 % APL Direct In-place 0.0 0.0 0 0.0 % APL Direct Cheap 0.0 0.0 5 0.1 % APL Direct Expensive 0.0 0.0 0 0.0 % DOL Deferred 0.0 0.0 0 0.0 % DOL Direct 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total Rows Updated 5.0 20.4 4604 9.3 %   Data Only Locked Updates Total Rows Updated 0.0 0.0 0 n/a ------------------------- ------------ ------------ ---------- ---------- Total DOL Rows Updated 0.0 0.0 0 0.0 %   Deletes APL Deferred 5.2 21.2 4793 98.8 % APL Direct 0.0 0.2 39 0.8 % DOL 0.0 0.1 19 0.4 % ------------------------- ------------ ------------ ---------- ---------- Total Rows Deleted 5.2 21.5 4851 9.8 % ========================= ============ ============ ========== Total Rows Affected 53.2 218.4 49351

---

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

Transaction Management ----------------------   ULC Flushes to Xact Log per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- by Full ULC 4.3 17.6 3970 76.5 % by End Transaction 0.3 1.0 236 4.5 % by Change of Database 0.0 0.1 15 0.3 % by Single Log Record 0.3 1.2 273 5.3 % by Unpin 0.6 2.5 566 10.9 % by Other 0.1 0.6 127 2.4 % ------------------------- ------------ ------------ ---------- Total ULC Flushes 5.6 23.0 5187   ULC Log Records 182.3 747.6 168954 n/a Max ULC Size During Sample n/a n/a 2048 n/a   ULC Semaphore Requests Granted 361.6 1483.2 335195 100.0 % Waited 0.0 0.0 1 0.0 % ------------------------- ------------ ------------ ---------- Total ULC Semaphore Req 361.6 1483.2 335196   Log Semaphore Requests Granted 9.1 37.3 8423 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- Total Log Semaphore Req 9.1 37.3 8423   Transaction Log Writes 8.8 36.0 8136 n/a Transaction Log Alloc 8.0 32.8 7422 n/a Avg # Writes per Log Page n/a n/a 1.09620 n/a   Tuning Recommendations for Transaction Management ------------------------------------------------- - Consider increasing the 'user log cache size' configuration parameter.

---

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

Index Management ----------------   Nonclustered Maintenance per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Ins/Upd Requiring Maint 5.0 20.6 4649 n/a # of NC Ndx Maint 74.5 305.5 69035 n/a Avg NC Ndx Maint / Op n/a n/a 14.84943 n/a   Deletes Requiring Maint 5.0 20.5 4633 n/a # of NC Ndx Maint 74.5 305.4 69019 n/a Avg NC Ndx Maint / Op n/a n/a 14.89726 n/a   RID Upd from Clust Split 0.0 0.2 35 n/a # of NC Ndx Maint 2.3 9.6 2170 n/a Avg NC Ndx Maint / Op n/a n/a 62.00000 n/a   Upd/Del DOL Req Maint 0.0 0.1 19 n/a # of DOL Ndx Maint 0.0 0.1 19 n/a Avg DOL Ndx Maint / Op n/a n/a 1.00000 n/a   Page Splits 0.8 3.3 749 n/a Retries 0.0 0.0 0 0.0 % Deadlocks 0.0 0.0 0 0.0 % Add Index Level 0.0 0.0 0 0.0 %   Page Shrinks 0.2 1.0 224 n/a Deadlocks % Deadlock Retries Exceeded 0.0 0.0 0 0.0 %   Index Scans per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Ascending Scans 59.1 242.2 54748 99.1 % DOL Ascending Scans 0.5 2.0 447 0.8 % Descending Scans 0.1 0.3 57 0.1 % DOL Descending Scans 0.0 0.0 0 0.0 % ------------ ------------ ---------- Total Scans 59.6 244.5 55252

---

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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128

Lock Management ---------------   Lock Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Lock Requests 828.0 3396.1 767522 n/a Avg Lock Contention 0.0 0.0 0 0.0 % Deadlock Percentage 0.0 0.0 0 0.0 %   Lock Detail per sec per xact count % of total ------------------------- ------------ ------------ ---------- ----------   Table Lock Hashtable Lookups 9.6 39.6 8941 n/a Avg Chain Length n/a n/a 0.01085 n/a Spinlock Contention n/a n/a n/a 0.0 %   Exclusive Table Granted 0.3 1.4 320 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total EX-Table Requests 0.3 1.4 320 0.0 %   Shared Table Granted 0.0 0.2 38 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Table Requests 0.0 0.2 38 0.0 %   Exclusive Intent Granted 0.2 0.8 188 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total EX-Intent Requests 0.2 0.8 188 0.0 %   Shared Intent Granted 9.0 37.0 8356 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Intent Requests 9.0 37.0 8356 1.1 %   Page & Row Lock HashTable Lookups 67.5 276.8 62559 n/a Avg Chain Length n/a n/a 0.00019 n/a Spinlock Contention n/a n/a n/a 0.0 %   Exclusive Page Granted 0.4 1.5 348 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total EX-Page Requests 0.4 1.5 348 0.0 %   Update Page Granted 0.4 1.8 404 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total UP-Page Requests 0.4 1.8 404 0.1 %   Shared Page Granted 64.2 263.5 59552 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Page Requests 64.2 263.5 59552 7.8 %     Exclusive Row Granted 0.1 0.3 58 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total EX-Row Requests 0.1 0.3 58 0.0 %   Update Row Granted 0.0 0.1 19 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total UP-Row Requests 0.0 0.1 19 0.0 %   Shared Row Granted 0.4 1.8 403 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Row Requests 0.4 1.8 403 0.1 %     Next-Key Total Next-Key Requests 0.0 0.0 0 n/a   Address Lock Hashtable Lookups 752.8 3087.8 697836 n/a Avg Chain Length n/a n/a 0.00077 n/a Spinlock Contention n/a n/a n/a 0.0 %   Exclusive Address Granted 613.1 2514.6 568310 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total EX-Address Requests 613.1 2514.6 568310 74.0 %   Shared Address Granted 139.7 573.1 129526 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total SH-Address Requests 139.7 573.1 129526 16.9 %     Last Page Locks on Heaps Granted 16.6 68.1 15396 100.0 % Waited 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total Last Pg Locks 16.6 68.1 15396 100.0 %     Deadlocks by Lock Type per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Deadlocks 0.0 0.0 0 n/a     Deadlock Detection Deadlock Searches 0.0 0.0 0 n/a     Lock Promotions Total Lock Promotions 0.0 0.0 0 n/a     Lock Timeouts by Lock Type per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Timeouts 0.0 0.0 0 n/a

---

Rien a redire du sp_sysmon si ce n'est les 2 points suivants:

1) les 38% de data qui ne se trouvent pas en cache (mais c'est peut-etre normal si la table en question n'est pas tres utilisee)

2) l'ULC a plus de 70%... la il vaudrait mieux la passer a 2048 (au cas ou elle est a 1024)

Merci pour votre aide