0

i was wondering how a sql update works in two scenarios:

table with 3 columns: a, b, c

say the data value for a has to be changed.

1: update on a row setting all columns a, b, c (where b and c have not changed from what is stored)

2: update on a row setting only the a column value (because b and c have not changed)

is there any performance difference between these? im having a hard time finding information about how they actually work behind the scenes. from the few articles i found my understanding is that in an update the db will:

  • load the row (all columns) into memory
  • set the values from the set tuple (in order)
  • write the updated row back
0

1 Answer 1

2

This is going to vary between databases, but there are things that we can say are generally (but certainly not always) true of common databases such as SQL Server, PostgreSQL, etc.

Drive IO generally works in blocks, usually these days of 4Kbyte or 0.5K, and to write any of a block the drive will write the whole block. Database engines organise their structures into pages for various reasons including to mach the IO subsystem. In MS SQL Server each page is 8K, done databases allow you to choose on a per DB or per server basis. As with the underlying drives writing to a page will result in rewriting the whole page (which may be multiple disk blocks in size) not just the part constituting the updated row(s) or the individual columns in the row(s). So the same amount of IO will be done in your two examples (SET a & SET a, b, c).

In fact more than just that page will be affected: an entry will be made in the transaction log first, and relevant index pages may need to be updated too. The MVCC model used (or supported as an option) by some database systems as extra details to the question, but that detail may be getting a bit deeper than the intended scope of your question.

Another point that does seem relevant is that this IO will happen even if nothing really changes: if you are setting column a to 42 and it is already 42 the writes will still happen. Further more, any relevant triggers will still fire, constraints will be checked, etc. This is why you sometimes see statements like UPDATE someTable SET aColumn = @aValue WHERE aColumn <> @aValue, to avoid excess activity (if doing this in your code, be sure to be careful to account for NULLs if they might be present, which my example does not for the sake of being concise, also be aware that there are circumstances where you might want triggers to fire even if an update otherwise effectively does nothing).

It is worth reiterating before finishing this answer, that none of this is true for all databases, or all IO more generally, but is only commonly true.

3
  • wow thank you for the detailed answer. can you clarify what a page relates to? a table, row (i think) or column? and to clarify this behavior means that whether i set the changed column or all columns (some changed some not) the IO / performance is the same? since the whole row is rewritten entirely in both cases?
    – vampiire
    Commented Dec 21, 2019 at 5:12
  • 1
    Related detailed information for SQL Server sql.kiwi/2010/08/the-impact-of-non-updating-updates.html
    – Paul White
    Commented Dec 21, 2019 at 7:47
  • 1
    @vampiire - a data page contains one or more rows, potentially hundreds, depending on the size of the data in those rows. MS have some online documentation on the internals (for instance learn.microsoft.com/en-us/sql/relational-databases/…) though if you are starting out something like Brent Ozar's "how to think like a database" series might be a good place to start brentozar.com/training/think-like-sql-server-engine before hitting dryer technical articles. Commented Dec 22, 2019 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.