Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query hangs; SQLcopied to new query works (2000)

    We have a query that links local tables to Oracle tables. An existing query is hanging. If I copy the SQL and paste it into a new query, it still hangs. If I refresh the links, then copy the SQL into a new query, it returns in less than 2 seconds.

    However, the new query is going bad as well. We haven't been able to track down what is causing it. The bad query is performing millions of round trips to the Oracle database. The good query is not. Why would 2 queries with the same SQL have different execution plans?
    Any ideas why this is happening?
    Any ideas on how to keep the working one working? Right now we are having to refresh the links & make a new copy everytime it goes bad.

    PS: I'm an Oracle DBA trying to research this Access problem, so I'm a novice Access user.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query hangs; SQLcopied to new query works (2000)

    Is this, I hope, a pass-through query?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Feb 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query hangs; SQLcopied to new query works (2000)

    Can it be a pass-through query if it is joining to a local table as well?

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query hangs; SQLcopied to new query works (2000)

    No. Pass-through queries can't include a join to local Access tables. Although, I believe you can create an Access query that joins an Access table to a pass-through query; although the result would not be updatable, if that is needed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Feb 2006
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query hangs; SQLcopied to new query works (2000)

    It does need to be updateable. Do you have any thoughts around why 2 queries with identical SQL would be handled differently?

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query hangs; SQLcopied to new query works (2000)

    >>Do you have any thoughts around why 2 queries with identical SQL would be handled differently?<<

    No I don't. Apparently the Access optimization is getting confused somehow by the Oracle tables. The only thing I can suggest is that you use a pass-through query as the basis for an append query to a local temp table in your Access frontend (if possible). Then use this to join to other Access tables in an updatable query.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query hangs; SQLcopied to new query works (2000)

    Sorry to be late to the party - but if you are joining a local table to a large Oracle table, that never works very well. Put the local table into Oracle, and link to it as well as the big table so the Oracle database engine can run the query for you. If you don't, Access has to pull in the entire Oracle table to do the join on the local table. I can't explain why it works occasionally, but then degrades - presumably you are linking via ODBC, and the ODBC driver you are using is involved somehow.
    Wendell

Posting Permissions

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