Table design question

Question

Table design question

kknudson

SSCarpal Tunnel

Points: 4986
More actions
April 22, 2004 at 7:58 am

#108282

I have 2 tables, linked on a policy number. I currently have defined as CHAR(12). This made loading etc. much easier.
The users really need 3 pieces, 3-6-3, and may search primarily on part 1 and 2 only. I meet with them tomorrow to get a better feel for how they will access the data.

Which would provide the best performance ?
Leaving as it is, and creating computed fields to give the users the 3 fields. I may need to provide indexes on part 1 and 2.
Or
Physically splitting them.
Splitting them will be somewhat difficult, the detail table is 220 million rows, and the space is somewhat limited on the server. But I can do it, I already did some data cleansing to the entire table. I had to do one year at a time.
Note this is a data warehouse type DB, with basically monthly updates only.

KlK

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

roberthamilton Ten Centuries Points: 1002 More actions · Answer 1

KIK

I think you're going to have to try both options, perhaps on a subset of your data, before deciding which route to take, since either is obviously valid.

In either case, you have the bulk of the maintenance overhead happening during data loading, which would be quite sensible given that you're only loading monthly.

Your decision is then down to query performance and storage space.

If the policy number is your primary key, then splitting the fields is probably going to be the best bet, since querying computed fields will involve and index search, followed by a bookmark lookup back to the primary key, which can be very expensive for large volumes of data.

Splitting the fields should also require less storage space, which may be important given the size of your dataset :

Split fields = 12 bytes storage + 12 bytes for the index = 24 bytes

Computed fields = 12 bytes storage + 9 bytes storage for the computed fields + 9 bytes for the indexes = 30 bytes

(Obviously I've left out the overheads of indexing, before anyone jumps in to point that out!!!)

So, I'm swaying towards split fields, but try it out first if you can.

Hope this helps

Regards

Rob

srinivas_mca2k SSC Journeyman Points: 93 More actions · Answer 2

Hi,

u r telling that in the 3 peices, only first two pieces is the PK. so cut the whole 12 chars to two pieces.

1. char(3 + 6) Piece1, primary key

2. char(3)

Venkata Srinivasa Rao