Performance with combined key in junction table

[Riley DeWiley]

I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.

I have a junction table, AB, containing fields AID, BID, and Count (a
number).

AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.

I need to do queries across AB from A to B and vice versa.

Would they be faster if AID and BID were separate keys, and I handled
uniqueness constraints myself?

Jet 4.0, OLEDB, C++, Windows.

RDeW

[Tom van Stiphout]

On Fri, 5 Aug 2005 20:14:42 -0700, "Riley DeWiley"
<ri***********@gmail.com> wrote:

Try it, and report back to us.

The PK in the junction table across both fields is the right thing to
do, so leave it.
I'm assuming you already created enforced relationships between the
tables. That creates hidden indexes behind the scenes, so you already
have at least an index on BID. Performance should already be optimum.

With compound indexes (those over more than one field), an additional
index over Field1 doesn't add value: the compound index is already
organized that way. In your case an index on Field2 is already taken
care of by your relationship to table B.

-Tom.

I have an application with two tables, A and B. Each has an autonumber
unique ID field, plus other data.

I have a junction table, AB, containing fields AID, BID, and Count (a
number).

AB has a combined unique key comprising AID and BID, that is, any
combination of the two must be unique, even though any given AID or BID may
exist in many records.

I need to do queries across AB from A to B and vice versa.

Would they be faster if AID and BID were separate keys, and I handled
uniqueness constraints myself?

Jet 4.0, OLEDB, C++, Windows.

RDeW