Are you allowed to be sizeist in the database world

Well even if your not, one of my real bug bears is fat tables. These aren't large tables they are fat tables, wide ones with lots of columns or large data types.

The reason this gets is is people develop databases with 10s of rows in them. Then end up with millions of rows in them in a live environment and find this things just don't perform.

Then managing those tables is a real chore.

Just because you have a table called person doesn't mean all the information relating to a person has to be in that table. Especially when you most of the time all you want is there name. Ok so you can create covering indexes, but then you hurt your insert performance and you can't create a covering index for every situation.

Imageine the situation you have a table that links people to stadium. you can have millions of people visit your stadium but you've only got a few stadium,

Do you have a table that is like this

create table StadiumVisit (personId uniqueidentifier not null
, visitDate datetime not null
, stadium int not null
, comments varchar(200))

create table StadiumVisit2 (personId int not null
, visitDate smalldatetime not null
, stadium tinyint not null)

Whats the different well the first would be ~a minium of  28 bytes per row and the other would be 9 bytes per row (ignoring overhead). With 10 million rows the second takes up 90Mb. Assuming the average comment was 100 bytes the first would take 1280Mb ~1.2Gb

How much memory you got in your box? 1Gb which one is going to perform better?

Even ignoring the comments field thats 3 times more data to read to process a query, the impact is that you put more pages into memory that you don't need, you push pages out of memory that are needed by other processes, you query needs to process more data and thus use more cpu. In a scalable system make sure you do as little as possible, this means it has less impact on anything else and so you server can do more.

For a great book on this look at Pro SQL Server 2005 Database Design and Optimization by Louis Davidson.

But be careful of fat tables, we all no how they get fat but I will leave that to another time.

Published Thursday, May 25, 2006 5:33 PM by simonsabin


No Comments