slow append query from a pass through query

[Aivars]

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

 

[S.Clark]

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.

 

[Dale Fye]

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

 

[Rick Brandt]

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.

 

[Aivars]

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