Access 2000 Query Execution Time

I have two tables like Parent-Child. In Parent table, there are 7211 records. In Child, there are 169498 records. Between these two tables, there is no relationship (means haven't set Foreign Key, but using parentID). In Child table, there are extra records and missing records.

As A sample,

Parent Table - tblParent
PID     PName
A001    John
B002    Mary
...     ...

Child Table  - tblChild
PID   TID   Desc
A001  056   Book
Y004  087   Pen
...   ...   ...

My query is like below,

SELECT PID
FROM tblParent
WHERE PID NOT IN
(
SELECT PID
FROM tblChild
)

Running it with actual no. of records, MS Access 2000 is halt suddenly. If i test it开发者_如何学Go with 10 records, it works properly. What is the main cause? No of records?

I try another way.

SELECT C.PID, P.PID
FROM tblChild C, tblParent P
WHERE C.PID <> P.PID

At this time, multiply result comes out. (I mean one C.PID with ALL P.PID and then etc...)

How could I get extra and missing records within shortest execution time in Access 2000?

Firstly, you do have an index on the PID column? It should be the primary key in tblParent and a non-unique index in tblChild. Setting the foreign key relationship would have created these indexes for you (I believe).

You could reduce the number of records that you need to match on. If you were to run this SQL:

SELECT DISTINCT PID
FROM tblChild
WHERE PID IN (
    SELECT PID
    FROM tblParent
)

You would only get the parent PID's that should be in the tblParent table. Then combining it like

SELECT PID
FROM tblParent
WHERE PID NOT IN
(
    SELECT DISTINCT PID
    FROM tblChild
    WHERE PID IN (
        SELECT PID
        FROM tblParent
    )
)

Should help speed up the query to find all parents with no children.

To find the extra children records, you could do

SELECT DISTINCT PID
FROM tblChild
WHERE PID NOT IN (
    SELECT PID
    FROM tblParent
)

Unfortunately, I don't have Access to test this out. I hope it helps

It's because you select 169 498 records in the subquery:

SELECT PID
FROM tblChild

That's a lot. Whole tblChild table is possibly scanned, read from disk, PID column is separated and stored somewhere in the memory, all 169 498 values. Then, you read all 7211 records and check for each if it's not in the huge 169 498-sized dataset of child PIDs. That's a lot of work for Access.