March 10, 2016 at 4:41 pm
Excellent article, thanks.
August 24, 2018 at 1:04 pm
Nice article, Hugo. I always enjoy reading your articles as they are so carefully and logically laid out.
One area that gets overlooked with RI is locking on the logical parent for deletes and inserts. If your database design employs declarative RI, please take this into consideration. For example, if you are attempting to insert a large number of logical child rows (i.e., with the same logical parent FK value), you will be continuously locking that parent table's row or table's data page. If that logical parent row is subject to updates, you will have contention, maybe even significant contention.
Hugo already commented on this, but I will add my own. I have too much bad experience the data resulting from tables that don't have proper RI constraints. A DBA's first duty is to the integrity of the data, not the performance of the transactions. If fast but wrong better than slightly slower, but correct. And in any case, as the article makes clear, FKs can assist performance. In large loads I will turn them off, but then will check them (as in the article) when they are re-enabled.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply