Thursday, August 29, 2013

Nothing Ever Always

Sometimes I get really frustrated by the things I used to know, but somehow forgot.
For instance, I USED to know that, in a relational database, nothing is ever always true.
Huh?

Perhaps an example will be illuminating.

Indexes - we are told that they help reads and hurt writes.  I suppose that's true, on some level, because of the processing the RDBMS has to do in order to perform the actual operation of writing to the table, and updating the indexes.

However, I had a situation today wherein I found myself creating an index to DRAMATICALLY speed up a write operation.

Both for a delete where and an update where, adding the index cut the execution (in DB2, measured in timerons) by 60 - 80 %.  Wow. 

Honestly, I should have known this.  I've been doing this for a while. I get used to pat ideas and the looking at a problem in a very particular way, or on a small set of data.

Bottom line, in relational databases, nothing ever always.

No comments:

Minimally Logged Operations in SQL Server

I'm posting this mostly so I can find it later :) The following article has a great table that defines when operations will be minimall...