Results 1 to 3 of 3
  1. #1
    debhead is offline Novice
    Windows XP Access 2000
    Join Date
    Dec 2010
    Posts
    4

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

    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


  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    how many records are there in each table?

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

  3. #3
    debhead is offline Novice
    Windows XP Access 2000
    Join Date
    Dec 2010
    Posts
    4
    Quote Originally Posted by weekend00 View Post
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Database size Grows too fast
    By no-e in forum Access
    Replies: 0
    Last Post: 12-16-2008, 02:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums