Speed up table with pseudo key

[Bruce]

I have a database with a header table and related details table with the following structure.

1) The Header table has a Primay Key Code of type text.
2) The detail table has a concatenated Primary Key of Code and Date which are type text and text.
3) The relationship has been established one to many with referential integrity enabled

However the detail table has 6 million records and queries can be slow. I have been reading that I should add a pseudo key that is indexed.

So I have added the following;

1) A field ID to the header table of type autonumber
2) A field ID in the detail table of type long int

What im not sure of is;
1) Do I need to change my primary key? Should is include the field ID
2) What relationships should be set for the use of the pseudo key.

Regards,

Bruce

 

[John Vinson]

What im not sure of is;
1) Do I need to change my primary key? Should is include the field ID

It is not obligatory to make the ID field the Primary Key; however, it
should have a unique Index in the main table (and a nonunique Index in
the many-side table, which you needn't create - see below). I'd make
it the Primary Key just for clarity, but keep a unique index on the
current Text field.

2) What relationships should be set for the use of the pseudo key.

Simply join the two ID fields. Access will automatically make it a one
to many relationship. Defining the relationship will create the
nonunique index on the foreign key field.

How big is the textfield? I'll be very interested to hear if the
performance is affected and by how much - 6,000,000 rows is an
impressive size for an Access table!

 

[Bruce]

Hi John

What I did in the end was leave my original primary keys intact/unchanged (as to maintain ref integ)

I broke to original relationship between the header and detail table and established this with the ID field

The speed improvement at a guess is about 3times at a guess, however this may be dependant on the following condition

1) Suitable when there are a extremely large no of row (i.e. 6 millions). I observed whilst populating this database that the beyond a certain point, the query time became expotential rather than linear
eg 1million records took 1 sec and 3 million took 10 sec

2) Of advantage when the primary key is of type text or a when the primary key is a concatenated index

Regards

Bruce