Chapter 14. Data Changes

The term data change appears in the SQL Standard document. It means any change to the database that is caused by INSERT, UPDATE, or DELETE statements. We often refer to such changes as updates (lowercase), but that's a bit confusing because usually UPDATE (uppercase) refers to the UPDATE statement alone. So we'll use "data change" the way the SQL Standard does: to mean an INSERT, an UPDATE, or a DELETE.

Data-change statements occur less frequently than SELECT statements, but a typical data-change statement is slow. For example, compare these two SQL statements:

 SELECT column1 FROM Table1   WHERE column1 = 12345 UPDATE Table1 SET column2 = 10   WHERE column1 = 12345 

Both statements must go through the same retrieval steps to find the matching rows, but the UPDATE will be slower, mainly for these reasons:

• The UPDATE must get an exclusive lock instead of a more-permissive shared lock; see Chapter 15, "Locks."

• The UPDATE must check any constraints or triggers.

• The UPDATE must add one, and possibly two, new entries in the log file.

• The UPDATE must shift following rows in the page up or down if the size of the updated row changes. An expanded row might also have to go on a different page.

• The UPDATE must delete and replace any index keys for the changed column.

• If a later COMMIT happens, some writing to disk will occur.

Changing a row always takes at least 3 times longer than retrieving it, and can take 100 times longer in worst-case scenarios. So it's worthwhile to examine the main performance difficulties.

It's impossible to deal with data-change statements in isolation. There are all sorts of effects that we discuss in other chapters, notably Chapter 8, "Tables," Chapter 10, "Constraints," and Chapter 15, "Locks." In this chapter, though, we'll deal only with matters that relate directly to data change: the data-change statements themselves (INSERT, UPDATE, DELETE) and the transaction-end statements (COMMIT, ROLLBACK).