[SYBASE] optimisation requete UPDATE

**metheorn** · 12/05/2004, 14h41

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 :

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

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

**Fabien Celaia** · 13/05/2004, 15h51

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

Soit

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
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

**metheorn** · 14/05/2004, 15h32

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

Code :

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

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 :

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
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.

**Fabien Celaia** · 14/05/2004, 16h37

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 :

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

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

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

...

**metheorn** · 19/05/2004, 17h20

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 :

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

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

**Fabien Celaia** · 20/05/2004, 09h29

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

Code :

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

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.

**metheorn** · 21/05/2004, 14h34

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 :

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
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 :

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
 
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 :

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
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 :

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
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 :

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
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 :

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
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

**Fabien Celaia** · 21/05/2004, 21h29

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)

**metheorn** · 24/05/2004, 17h01

Merci pour votre aide

[SYBASE] optimisation requete UPDATE

Sybase

Vue hybride

Discussions similaires

Partager

Partager