Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Access97 using poorly built queries

    We experienced a situation where a user was slogging through some data linked into MSAccess97 from Oracle. The queries were running really slow. I used TOAD to investigate what those queries looked like, and instead of seeing a SQL statement like:

    Code:
    /* Formatted on 10/1/2009 2:07:17 PM (QP5 v5.115.810.9015) */
    SELECT   "RSNAD",
             "RSNEB",
             "RSNBB",
             "DATE_APPLIED",
             "DATE_OF_DEPOSIT",
             "AMOUNT_PAID",
             "INTO_FLAG",
             "OUT_OF_FLAG",
             "VALIDATION",
             "CREATE_DATE",
             "CREATE_ID",
             "MOD_DATE",
             "MOD_ID"
      FROM   "BCDLIC"."IB_ADJUSTMENTS"
    I saw one which looked like

    Code:
    /* Formatted on 10/1/2009 2:07:17 PM (QP5 v5.115.810.9015) */
    SELECT   "RSNAD",
             "RSNEB",
             "RSNBB",
             "DATE_APPLIED",
             "DATE_OF_DEPOSIT",
             "AMOUNT_PAID",
             "INTO_FLAG",
             "OUT_OF_FLAG",
             "VALIDATION",
             "CREATE_DATE",
             "CREATE_ID",
             "MOD_DATE",
             "MOD_ID"
      FROM   "BCDLIC"."IB_ADJUSTMENTS"
     WHERE      "RSNAD" = :V001
             OR "RSNAD" = :V002
             OR "RSNAD" = :V003
             OR "RSNAD" = :V004
             OR "RSNAD" = :V005
             OR "RSNAD" = :V006
             OR "RSNAD" = :V007
             OR "RSNAD" = :V008
             OR "RSNAD" = :V009
             OR "RSNAD" = :V010
    It's like a setting was enabled which caused Access to start grabbing data in blocks of 10 records. Is there a way to switch back?

    --=Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Looks like table which have primary keys defined, have that attribute included in their linked table definition, and that causes MSAccess97 to come up with the poor query plans.

    If anyone has an idea on how to disable this, that would be awesome. We must've performed an unintentional change during a Citrix upgrade - or something - which altered the behavior of the ODBC driver? Ugh ...

    --=Chuck

Posting Permissions

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