why isn't this query FAST!? I've tried EVERYTHING! (query performance issue)

[Bob]

microsoft . public . access . queries
sub: why isn't this query FAST!? I've tried EVERYTHING!

running access 2k; in frontend-backend configuration.

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.
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:
T_inv_log T_inv_conv T_ord_det T_inv_mast
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;



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.
Whatever arrangement I've tried - the FINAL query STILL TAKES FOREVER
TO RUN????

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

TIA - Bob

PS-
upset that this posts w/ a kerned font - had this perfectly formated
for fixed font

 

[John Spencer]

I suspect that "BOTH backends" is the problem. You are relating across data
files and when you do that lose the efficiency of the joins since you
basically end up doing a table scan and probably have to bring back all the
records from the tables involved in the join between t_inv_Conv and t_Ord_det.
On the other hand, you did say that a query with just those two tables was fast.

I assume you have a good reason for having two separate databases. Is there
any possibility of combining the two mdb into one mdb?

From what you said I assume you did try nesting queries.
Query 1 First two tables
Query 2 Last two tables
Query 3 joining query 1 and query 2. I doubt that would help since query 3 is
probably getting the same query plan as your 4 table query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Bob]

John-

TYVM for your reply.

I unfortunately can not join the 2 backends - however, as you've
already noticed, that isn't the problem as a query with 1 table from
each backend still runs instantly..
In fact - I've even imported all 4 tables into the frontend app - with
EXACTLY the same results!! This experiment, more than anything else
has me completely perplexed. I wanted to eliminate any networking
issues from the equation - so this proves the problem is somewhere in
access (and/or the design of my query).

Your other assumption is also correct - I've tried the "query 1, 2, >
3" approach with again, same results.
This approach also has me confused - as query's 1 & 2 are saved,
static query's, one would think that a 3rd saved query which utilizes
ONLY those 2 would be just as fast (almost) as each of the component
queries.

FYI- the 2 backend's actually feed 2 completely different apps, which
THEORETICALLY (at least @ design phase) weren't supposed to interact.
Later feature requests have forced a tiny bit of cross-over, but they
are still 2 separate and distinct apps. And - one app does not do any
writes to the other app's backend - R/O.

Hopeful that you might have any other idea's I've overlooked....

TX again- Bob

 

[Bob]

Curious followup.....
I've been running the app from a mapped network resource.

if I move the app with the imported tables to my local drive - the
query runs instantly.
if I move the app with the linked tables to my local drive - it takes
forever.

I'm not so surprised that the first scenario works correctly - But I'm
even more confused as to how ANY combination of two of the tables in
the query works instantly; and the final combination works so
horridly.

BTW - users run this app from a private (to each of them) mapped
resource on the network.
I do NOT allow local copies of apps due to privacy regulations.
(not that a local, linked copy works any better)

 

[croy]

Curious followup.....
I've been running the app from a mapped network resource.

if I move the app with the imported tables to my local drive - the
query runs instantly.
if I move the app with the linked tables to my local drive - it takes
forever.

I'm not so surprised that the first scenario works correctly - But I'm
even more confused as to how ANY combination of two of the tables in
the query works instantly; and the final combination works so
horridly.

Does the app's filename have more than 8 characters? If so,
try cutting it down to the 8+3 convention and see if it's
any faster.

 

[Bob]

Does the app's filename have more than 8 characters? If so,
try cutting it down to the 8+3 convention and see if it's
any faster.

Hi Croy;

Tx 4 U'r reply....

actually - the app and both backend's all conform to the 8.3 layout -
as do all directory's in their respective path's.