473,225 Members | 1,277 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

DB2 table design and performance considerations

This question is about DB2 table design and performance.

We are using DB2 UDB Enterprise 8.2 on Linux.

We have 6 tables in a parent-child (one-to-many) relationship with each other. Each child table having an FK up to its parent. This particular design is motivated by our domain model.

A -> B -> C -> D -> E -> F

There is some debate in our team about how best to accomplish this. Two approaches have been discussed.

One alternative is to have a unique ID (UID) as a DECIMAL(0,19) for each PK/FK combination and then do selects for each table based only on this key. I have paraphrased the pseudo SQL as follows:

create table A ( A_UID PK not null, ...):
create table B ( B_UID PK not null, A_UID FK, ...);
create table C ( C_UID PK not null, B_UID FK, ...);
create table D ( D_UID PK not null, C_UID FK, ...);
create table E ( E_UID PK not null, D_UID FK, ...);
create table F ( F_UID PK not null, E_UID FK, ...);

Another approach is to use a UID only at the top table and then have sequence numbers (0,1,2,3, ...) as INTEGER for each additional table as we go down. Therefore to access the top table all we need is a single key.

To select rows from the second table we would need the composite key (FK, SEQ_NO1). To access the third table, C, we would need the composite key (FK, SEQ_NO1, SEQ_NO2). To access the 6th table we would need the composition key (FK, SEQ_NO1, SEQ_NO2, SEQ_NO3, SEQ_NO4, SEQ_NO5). In this case we need all the sequence numbers to fully specify the records we want from the table. Again here is some pseudo SQL which represents the design:

create table A ( A_UID PK not null, ...);
create table B ( B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (B_SEQ_NO, A_UID)

create table C ( C_SEQ_NO not null,
B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (C_SEQ_NO, B_SEQ_NO, A_UID)

create table D ( D_SEQ_NO not null,
C_SEQ_NO not null,
B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (D_SEQ_NO, C_SEQ_NO, B_SEQ_NO, A_UID)

create table E ( E_SEQ_NO not null,
D_SEQ_NO not null,
C_SEQ_NO not null,
B_SEQ_NO not null,
A_UID FK, ...);
PK is composite of (E_SEQ_NO, D_SEQ_NO, C_SEQ_NO, B_SEQ_NO, A_UID)

The belief is that DB2 will already have these records in the same set of pages for the second approach but will not have them for the first approach. So, page caching comes into play for second approach but not the first. The intent is to do the inserts for the cascade with 1 insert statement. So the first approach seems reasonable. But, I would think if the records are scattered on the disk, inserts come in at random times then the second approach is more reasonable.

Hence it is reasoned the second approach will be more performant. Or is it? Does having composite keys cost more?

Do these arguments have any merit?

Thank-you very much in advance.
Apr 22 '09 #1
0 2430

Sign in to post your reply or Sign up for a free account.

Similar topics

by: toedipper | last post by:
Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...
by: Gaurav | last post by:
http://www.sys-con.com/story/print.cfm?storyid=45250 Any comments? Thanks Gaurav
by: duoduo | last post by:
I want to know one thing, what is best design for tablespace containers, of course, it is DB2 EE 7.2 on AIX 4.3.3. For example, I have RAID 0+1 storage (8 disks) on EMC Symmetrix. Now I need to...
by: Will Honea | last post by:
I just got stuck with cleaning up a mess of records for a group I belong to - circa 80k memebers - that are being kept on a variety of spreadsheets and Access databases by a bunch of different...
by: Damon Hart | last post by:
Hi all - I am experiencing continually degrading performance on queries run against the special system tables. I notice the slowdown when these meta-data queries are run implicitly "behind the...
by: Jonathan Wood | last post by:
I have an idea for several Web sites that would involve articles that can be read. Article lengths will vary from short to a little longer, but nothing really long. I'm wondering at what point...
by: Johan Tibell | last post by:
I would be grateful if someone had a minute or two to review my hash table implementation. It's not yet commented but hopefully it's short and idiomatic enough to be readable. Some of the code...
by: Tony | last post by:
How much bloat does the STL produce? Is it a good design wrt code bloat? Do implementations vary much? Tony
by: neelsmail | last post by:
Hi, I have been working on C++ for some time now, and I think I have a flair for design (which just might be only my imagination over- stretched.. :) ). So, I tried to find a design...
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.