why isn't this query FAST!? I've tried EVERYTHING!

[debhead]

running access 2k; in frontend-backend configuration (actually front-end > (2) backends).

backends look like this: (FWIW- this representation is vastly simplified for discussion, so don't try to make sense of a topology that looks strange (naturally, all tables contain MANY more fields)

invtrack.mdb
T_inv_log
PK case_id (text,10; no dup)
T_inv_conv
PK ID (long auto; no dup)
IX case_id (text,10; dup ok)
IX box_id (text,10; dup ok)

sales.mdb
T_ord_det
PK ord_num (long; dup ok)
PK item_no (long auto; no dup)
IX item_id (text,10; no dup)
IX item_code (text,10; dup ok)
T_inv_mast
PK item_code (text,10; no dup)
item_name (text)

Now - the front-end app has links to tables in both back-ends.
ALL subdatasheet names are set to none - even the linked one's in the front-end (TYVM AB.
I can do a query (select *) for each of the 4 tables, and (no surprise) they run INSTANTLY.
My problem query has the general config of: (sorry for dots - my spaces R getting munched)
T_inv_log.....T_inv_conv.....T_ord_det.....T_inv_m ast
case_id-----(>)case_id.......item_code---->item_code
................box_id------->item_id.......item_name

if I run the following query - IT TAKES FOREVER!!

SELECT T_inv_log.*, T_inv_mast.item_name
FROM (T_inv_log INNER JOIN T_inv_conv ON
T_inv_log.case_id = T_inv_conv.case_id) LEFT JOIN
(T_inv_mast RIGHT JOIN T_ord_det ON
T_inv_mast.item_code = T_ord_det.item_code) ON
T_inv_conv.box_id = T_ord_det.item_id;

If I remove the last (or first) table - it still takes forever to run:
SELECT T_inv_log.*
FROM (T_inv_log INNER JOIN T_inv_conv ON
T_inv_log.case_id = T_inv_conv.case_id) LEFT JOIN
T_ord_det ON T_inv_conv.box_id = T_ord_det.item_id;

But - the first 2 tables alone runs instantly: (as do the last 2 tables, AND middle 2)
SELECT T_inv_log.*
FROM T_inv_log INNER JOIN T_inv_conv ON
T_inv_log.case_id = T_inv_conv.case_id;

in fact, ANY 2 tables works instantly.

I have tried just about EVERY permutation I can think of:
-different joins (outer's, inner's) in different combinations.
-placing various table sets in sub-query's within the same query.
-placing various table sets in separate query's, referenced in a 3rd Q.
Whatever arrangement I've tried - the FINAL query STILL TAKES FOREVER TO RUN????

other information:
-NO file names or directory names in related path's exceed 8.3 format.
-both backends are in a semi-common, but restricted network resource mapped to a local drive.
-frontend is run from each users' private area on another mapped network resource (can not have anything local due to privacy policies).
-virus software has exclusions for frontend/backend; so no scanning to slow things down.
-EVEN if I import the tables into the frontend app, - NO DIFFERENCE!!!???


-there are 2 backends because they represent 2 completely different apps, with a hint of crossover.


can anyone PLEASE offer a suggestion or idea as to why this is sooooo slow!?!?

TIA - Bob

[weekend00]

how many records are there in each table?

is it still slow if you use all INNER JOINs and no outer join?

[debhead]

how many records are there in each table?

is it still slow if you use all INNER JOINs and no outer join?

hi W.E. & TX for your reply...

2 of the tables have 30k or more records.
I can not do all inner joins (except for the 1st tbl to 2nd), because rest are 1 to many relationships.