slow append query from a pass through query

Discussion in 'Microsoft Access Queries' started by Aivars, Sep 21, 2007.

  1. Aivars

    Aivars Guest

    Hello,
    I have a pass through (ODBC?) query Query1 from Access 2007 to
    Interbase 5.xx remote server/database. This query when run separately
    is very quick. It is very simple - 'SELECT * from table1'. Table1 is
    a table in remote Interbase database

    When I try to update the Access 2007 table using update query Query2
    like:
    "INSERT INTO localtable SELECT * from Query1" it becomes very slow. If
    I try to update the table from a local data updating is immediate -
    very quick. As I mentioned - when run separately the pass through
    query also is very quick. The number of records is about 8000 only.

    Why when combined the whole thing becomes so slow? The number of
    records is growing so the reason I am asking is if it is so slow with
    only 8000 records what will happen if there are 50 000 records?

    Let's say if pass through query separately takes approx. 1 second then
    with updating local table it takes 8 - 10 seconds.

    No indexes are on local tables

    Thanks

    Aivars
     
    Aivars, Sep 21, 2007
    #1
    1. Advertisements

  2. Aivars

    Guest Guest

    Does Table1 have many indexes? If so, they may be causing a slowdown.
    Using the * is not optimal for performance either.

    Most times, when I'm doing a data bridge like this, I don't try to push the
    data directly into the destination. Instead, import the data into a temp
    table, that has no indexes and is more native to the original data. From
    that table, append the data to the final destination. There is also an
    opportunity to perform data scrubbing from the import to the final dest.

    --
    Steve Clark,
    Former Access MVP
    FMS, Inc
    http://www.fmsinc.com/consulting

    "Aivars" wrote:
    > Hello,
    > I have a pass through (ODBC?) query Query1 from Access 2007 to
    > Interbase 5.xx remote server/database. This query when run separately
    > is very quick. It is very simple - 'SELECT * from table1'. Table1 is
    > a table in remote Interbase database
    >
    > When I try to update the Access 2007 table using update query Query2
    > like:
    > "INSERT INTO localtable SELECT * from Query1" it becomes very slow. If
    > I try to update the table from a local data updating is immediate -
    > very quick. As I mentioned - when run separately the pass through
    > query also is very quick. The number of records is about 8000 only.
    >
    > Why when combined the whole thing becomes so slow? The number of
    > records is growing so the reason I am asking is if it is so slow with
    > only 8000 records what will happen if there are 50 000 records?
    >
    > Let's say if pass through query separately takes approx. 1 second then
    > with updating local table it takes 8 - 10 seconds.
    >
    > No indexes are on local tables
    >
    > Thanks
    >
    > Aivars
    >
    >
     
    Guest, Sep 21, 2007
    #2
    1. Advertisements

  3. Aivars

    Guest Guest

    Why is it necessary to import the data at all? Unless it is being deleted
    from its source, I rarely see a need to import data from an external source.

    Can you create a link to the data from Access? If so, you don't even need
    to do a pass-thru query.

    Dale
    --
    Email address is not valid.
    Please reply to newsgroup only.


    "Aivars" wrote:

    > Hello,
    > I have a pass through (ODBC?) query Query1 from Access 2007 to
    > Interbase 5.xx remote server/database. This query when run separately
    > is very quick. It is very simple - 'SELECT * from table1'. Table1 is
    > a table in remote Interbase database
    >
    > When I try to update the Access 2007 table using update query Query2
    > like:
    > "INSERT INTO localtable SELECT * from Query1" it becomes very slow. If
    > I try to update the table from a local data updating is immediate -
    > very quick. As I mentioned - when run separately the pass through
    > query also is very quick. The number of records is about 8000 only.
    >
    > Why when combined the whole thing becomes so slow? The number of
    > records is growing so the reason I am asking is if it is so slow with
    > only 8000 records what will happen if there are 50 000 records?
    >
    > Let's say if pass through query separately takes approx. 1 second then
    > with updating local table it takes 8 - 10 seconds.
    >
    > No indexes are on local tables
    >
    > Thanks
    >
    > Aivars
    >
    >
     
    Guest, Sep 21, 2007
    #3
  4. Aivars

    Rick Brandt Guest

    Aivars wrote:
    > Hello,
    > I have a pass through (ODBC?) query Query1 from Access 2007 to
    > Interbase 5.xx remote server/database. This query when run separately
    > is very quick. It is very simple - 'SELECT * from table1'. Table1 is
    > a table in remote Interbase database
    >
    > When I try to update the Access 2007 table using update query Query2
    > like:
    > "INSERT INTO localtable SELECT * from Query1" it becomes very slow. If
    > I try to update the table from a local data updating is immediate -
    > very quick. As I mentioned - when run separately the pass through
    > query also is very quick. The number of records is about 8000 only.
    >
    > Why when combined the whole thing becomes so slow? The number of
    > records is growing so the reason I am asking is if it is so slow with
    > only 8000 records what will happen if there are 50 000 records?
    >
    > Let's say if pass through query separately takes approx. 1 second then
    > with updating local table it takes 8 - 10 seconds.
    >
    > No indexes are on local tables


    When you view the result of the passthrough you are only seeing a few pages of
    records, not the whole thing. Access doesn't wait for all records to be
    retrieved before it shows you something.

    Try hitting the go to last record button when you view that query and you will
    likely find that it takes quite a bit longer. The append query has to wait
    until all records have been retrived before it can finish. Also when doing the
    append query you are *writing* and that always takes longer than reading.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
    Rick Brandt, Sep 22, 2007
    #4
  5. Aivars

    Aivars Guest

    Thanks a lot, Dale and Rick,

    Dale, I agree with you. To be honest, the reason I am importing data
    is that I can basically do the other stuff (queries, design of reports
    and forms, Excel reports from queries made in access) on a machine
    which cannot access the Interbase server (due to security policy in
    the company) from outside. That's why i need the data disconnected
    from ODBC source.
    I have no possibility whatsoever to do anything on Interbase (stored
    procs and functions) server. Also, sometimes the connection with
    server is terribly slow.
    Maybe when my reporting solution is more or less working I will use
    linked tables without imported data

    Rick, you are right. pass through query actually takes more time than
    i thought when tested as you suggested.

    I am Access noob and still learning.

    aivars
     
    Aivars, Sep 22, 2007
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MikeS

    append queries converted to SQL:pass-through on close

    MikeS, Nov 6, 2003, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    156
    MikeS
    Nov 6, 2003
  2. Vance

    Append Query... Can't Create a Unique Number to Append

    Vance, Dec 10, 2003, in forum: Microsoft Access Queries
    Replies:
    8
    Views:
    361
    Michel Walsh
    Dec 17, 2003
  3. Guest

    Link CSV file created query append query to append data

    Guest, Jul 1, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    640
    Guest
    Jul 5, 2005
  4. ryan05 via AccessMonster.com

    Updating Multiple Pass-Through Queries through a Form

    ryan05 via AccessMonster.com, Mar 24, 2006, in forum: Microsoft Access Queries
    Replies:
    5
    Views:
    327
    Duane Hookom
    Mar 28, 2006
  5. mcl
    Replies:
    3
    Views:
    1,460
Loading...

Share This Page