Joins and Query Speed

[Bev]

I am attempting to append records to a table. I have a table in my database
that contains a member ID. This single field table has a primary key on the
member id. It contains approximately 5,000 records.

It is joined to a linked table on an AS/400 platform with the same field and
it is also indexed. This table contains millions of records.

When I run the query it has run for days without finishing.
If I run the query with the criteria filled in for select member ids the
query will run. But when joining to the table, it does not finish.

Any suggestions on improving the performance of this query.

 

[vanderghast]

Make 2 queries:


SELECT *
FROM as400table
WHERE memberID = parameter


saved as, say, q1, then


SELECT *
FROM q1 INNER JOIN otherTable
ON q1.memberID=otherTable.memberID


And use that second query.


Vanderghast, Access MVP