PC Review


Reply
Thread Tools Rate Thread

slow append query from a pass through query

 
 
Aivars
Guest
Posts: n/a
 
      21st Sep 2007
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

 
Reply With Quote
 
 
 
 
=?Utf-8?B?Uy5DbGFyaw==?=
Guest
Posts: n/a
 
      21st Sep 2007
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
>
>

 
Reply With Quote
 
 
 
 
=?Utf-8?B?RGFsZSBGeWU=?=
Guest
Posts: n/a
 
      21st Sep 2007
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
>
>

 
Reply With Quote
 
Rick Brandt
Guest
Posts: n/a
 
      22nd Sep 2007
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


 
Reply With Quote
 
Aivars
Guest
Posts: n/a
 
      22nd Sep 2007
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






 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Is It Possible To Pass Parameters to A Pass Through Query mcl Microsoft Access Queries 3 21st Sep 2007 01:15 AM
How to determine is a stored procedure finished running through a pass-through query William Microsoft Access VBA Modules 1 8th Apr 2006 12:41 PM
Pass through authentication through 2 domains?? MEI Microsoft Windows 2000 Active Directory 1 13th Sep 2005 01:35 AM
Link CSV file created query append query to append data =?Utf-8?B?ZXNwYXJ6YW9uZQ==?= Microsoft Access Queries 2 5th Jul 2005 04:49 PM
append queries converted to SQL:pass-through on close MikeS Microsoft Access Queries 0 6th Nov 2003 08:34 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:02 PM.