Tuesday, March 21, 2006

Jolt winner - Database Engines and data tools

And the winner is... click
Can you guess without clicking? :-D

Monday, March 20, 2006

One big lookup table vs many small lookup tables

Recently I was reading SQL Magazine when I came across an article "Designing for performance: lookup tables" (instadoc: 48811 - Michelle A Poolet). The question was whether or not one big lookup table was better than many small ones.

While I do agree that one big lookup table complicates things in terms of integrity and data management there are a couple of arguments I disagree with.

Locking and blocking increases

The nature of a lookup table is read-only and given this I would suspect that only shared locks are being taken on the lookup table. So how would you experience locking and blocking in such a scenario?

You create a hotspot on the disk

I don't see why you would have hotspots on a read-only lookup table. Being a hot table means that all the pages of that table would probably be in your data cache - meaning less disk I/O once the pages have been cached. You can even force the table to stay in memory by pinning it, but I don't think many people actually use this technique since SQL Server handles data caching quite well.

I think like any database question the only answer here is: "it all depends". We have many domain specific lookup tables but we also have a big lookup table with over 300 different types. In a database with 400 tables I wouldn't want to create 300 additional tables for simple lookups where most of them would contain 3 to 4 records.