Results 1 to 3 of 3

Pass through Query slow

This is a discussion on Pass through Query slow within the Microsoft Access forums, part of the Question Forums category; I'm seeing some strange behavior when I run a pass through query. I am passing it through a Teradata database. ...

  1. #1
    Board Regular
    Join Date
    Apr 2004
    Location
    New Jersey
    Posts
    887

    Default Pass through Query slow

    I'm seeing some strange behavior when I run a pass through query. I am passing it through a Teradata database. When I execute the queries the records seem to appear almost instantly in the query reults window, but if you try to go to the last record (ctrl+down arrow) or copy the contents an hourglass appears and I sometimes have to wait for 3-4 mins for it to go to the last record.

    I'm just wondering if I have asetting wrong, or string to database setup wrong? I have the access databae in SQL ANSI92 mode.

    Thanks
    There is always a way...

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,198

    Default Re: Pass through Query slow

    If you try to go to the last record it will definitely be slow. I think the issue is that Access needs to process all the records individually for you to be able to move through them.

    Typically, I use pass-through to get the data I need, but then use a make-table query to create a local copy which I can then manipulate at will. For better performance, the make-table query should operate *only* on data from the remote source, without using any Access-s[ecific functions.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    Board Regular
    Join Date
    Dec 2007
    Location
    Houston
    Posts
    415

    Default Re: Pass through Query slow

    I have had this problem when I had databases greater than a few 100,000 lines.

    Issues are: RAM capacity and chip speed.

    My solution if you are the end user and everyhting flows correctly, without purchasing more RAM (but the investment is worth it if you use this info routinely and the databases are large):

    Instead of using the end of data feature, type the line number you want to go to, as you do in Excel A56000.

    I went in incriments of 100,000

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com