Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    1

    Unanswered: SQL: Paradox to Access

    We are converting an application from Paradox to Access. Almost everything works now, but there are some areas where we are having problems. The following SQL is generated from a routine. It works ok in Paradox but in Access, it is between 10 to 50 times slower.

    Code:
    SELECT * FROM LiveTFile STbl WHERE STbl.PDOC IN
    (SELECT DISTINCT PolTbl.PDOC FROM LivePolicyS PolTbl WHERE
    (PolTbl.PDOC > 0) AND (PolTbl.EX_DATE IS NOT NULL) AND 
    (PolTbl.EX_DATE <= #12/31/2002#) AND (PolTbl.STATUS NOT LIKE "ACTIV")) AND
    STbl.PDOC NOT IN (SELECT DISTINCT TFTbl.PDOC FROM LiveTFile TFTbl WHERE
    ((TFTbl.PDOC > 0))) AND (STbl.UniqueField BETWEEN 305 AND 10304) AND
    (STbl.UniqueField NOT IN (SELECT DISTINCT Tbl1.UniqueField FROM 
    LiveTFile Tbl1, TFile Tbl2 WHERE (Tbl1.UniqueField BETWEEN 305 AND 10304) AND
    (Tbl1.UniqueField = Tbl2.UniqueField)));
    
    The part in red is the part that's causing the slowdown. The person who wrote this is long gone, so we have no way to know what he was doing. No one here knows what that section does.

    Does anyone here have any idea what that section is supposed to do?

    Mike
    Hayden, ID

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    (STbl.UniqueField NOT IN (SELECT DISTINCT Tbl1.UniqueField FROM
    LiveTFile Tbl1, TFile Tbl2 WHERE (Tbl1.UniqueField BETWEEN 305 AND 10304) AND (Tbl1.UniqueField = Tbl2.UniqueField)));

    It is invoking a selection criteria based on a sub query. In Access, this causes the sub query to be run for every record in the main query (which I have no problem believing would be very slow).

    The sub query is: Select distinct values from Tbl1 where the uniquefield in between 2 values and doesn't match the uniquefield in Tbl1.

    I noticed that there are several record by record invoked queries - the whole setup is slow in Access. The part in red (if that really is the slow part) is likely the culprit simply because there's more data to group and sort (the DISTICT operator is a grouping command).

    My suggestion is to create the sub queries as stand alone queries, determine exactly what each yields and then use thm (or a simplification of) as a source in your main query instead of invoking it record by record as currently written.

    good luck,
    tc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •