Update


An update statement must relocate the changed index entries to maintain the index order. For that, the database must remove the old entry and add the new one at the new location. The response time is basically the same as for the respective delete and insert statements together.

The update performance, just like insert and delete, also depends on the number of indexes on the table. The only difference is that update statements do not necessarily affect all columns because they often modify only a few selected columns. Consequently, an update statement does not necessarily affect all indexes on the table but only those that contain updated columns.

Figure 8.3 shows the response time for two update statements: one that sets all columns and affects all indexes and then a second one that updates a single column so it affects only one index.

Figure 8.3. Update Performance by Indexes and Column Count


The update on all columns shows the same pattern we have already observed in the previous sections: the response time grows with each additional index. The response time of the update statement that affects only one index does not increase so much because it leaves most indexes unchanged.

To optimize update performance, you must take care to only update those columns that were changed. This is obvious if you write the update statement manually. ORM tools, however, might generate update statements that set all columns every time. Hibernate, for example, does this when disabling the dynamic-update mode. Since version 4.0, this mode is enabled by default.

When using ORM tools, it is a good practice to occasionally enable query logging in a development environment to verify the generated SQL statements. The tip entitled “Enabling SQL Logging” has a short overview of how to enable SQL logging in some widely used ORM tools.

Think About It

Can you think of a case where insert or delete statements do not affect all indexes of a table?

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
69
views
0
votes
0
answers
321
views

Fanout in R-Tree

Mar 27 at 08:07 jamie 1
tree indexing
0
votes
1
answer
104
views

Think About It

Mar 26 at 12:54 Markus Winand ♦♦ 511
reflection