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 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

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