ODBC Performance Help Needed

R

Rivdandi

How can I improve performance when I am running a query which joins a local
table to retrieve data from a table through an ODBC connection? Can I use a
pass thru, do I need to write VB to perform the query to speed it up? Any
thoughts would be appreciated.
 
V

Van T. Dinh

Not Pass-Through Query since the (remote) ODBC server doesn't know anything
about your local Table.

One possibilty is to create a Pass-Through Query to minimize the number of
rows to be select from the ODBC server and then a second (non-Pass-Through)
Select Query using the Pass-Through Query and your local Table as the
Sources. Whether this is possible or not depends on how you the local Table
related to the data you need from the ODBC server.

I don't think VB/VBA can help in this case because most of the delay will be
with the transfer of data from the ODBC server to your desktop.
 
A

Albert D. Kallal

Of course, when doing a join between different data sources, not much can be
optimized.

You could putt the data from the odbc source into a temp local table..and
then join on that.....

Of course, it is not clear if this is for a report that gets run every so
often, or if this join is part of a daily editing/production environment.
(temp tables are source of bloat..and this would need to use a temp mdb file
that you delete when you are done doing this).
 
T

Tony Toews

Albert D. Kallal said:
You could putt the data from the odbc source into a temp local table..and
then join on that.....
Click to expand...

I found that this worked quite well as the performance on the ODBC
connected tables sucked big time. In this case it was quite suitable
to refresh the temp tables on a daily or weekly basis so that's
exactly what we did.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
R

Rivdandi

I created a single pass through to retrieve data and it seems to run much
faster. My problem now is that the pass through is a select so I do not get
the results stored on a table. How can I get the results of my pass through
to be stored on a local table? Is this possible?
 
V

Van T. Dinh

Create a Make-Table Query using the Pass-Through Query as the DataSource.

--
HTH
Van T. Dinh
MVP (Access)
 
Top