I am currently in the process of designing tables for a specific application. I landed up with a table which has roughly around 50 fields. On analysis I found that 25 fields were frequently used during processing while the balance 25 fields were required only ocassionally.
The doubt is, If I split the table into two tables having a 1 to 1 relation so that the record length of frequently used table is small, will it give any advantage in performance over the usage of table having all 50 fields.
There would be a 2:1 performance advantage on full table scans on the frequently used data, assuming the table would have half the size. Whether this is significant depends on how big the table will be, how frequently you will perform FTS's against it, and how fast those queries need to be.
If the rarely-used data is only applicable to a small subset of the rows, then that might mean you'd get a performance advantage on queries on the rarely-used data by keeping it in a much smaller table - again, this will only be significant if data volumes are high.
If this is logically 1 100-column table, then I would be inclined to keep it that way. I would only consider splitting it if:
- it is a HUGE table, and
- it will be heavily queried, and
- experiments showed that it was likely to be a performance bottleneck, and
- you could verify by experimentation that the split tables would improve performance significantly
What are you going to do with your database? Is it OLTP database or do you plan to run tons of queries and have it more like a ROLAP?
Simply say, if you have let's say calling center and each user will see mostly one record (one customer), then I'd say do not brake it - you will get only more work.... if you plan to run lot of analytical queries let's say total balance for all west cost stores in 2002, then the answer can be split or create another read-only analytical table (star or snow flake schema). Everything also depends of the field types....
personally I'm not a big friend of split, but it can speed up everything on really huge tables (millions of records).