By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,052 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,052 IT Pros & Developers. It's quick & easy.

Doing a lookup with a small local table and a large ODBC table

P: n/a
I am trying to look up data on about 1000 records from a 6 million
record view on a DB2 database using msaccess.

The 6 million record DB2 view is connected through a linked ODBC table
(table A), and the 1000 primary key numbers I want are in a local
msaccess table (table B).

The most obvious solution is to join table A and B, but this is
excruciatingly slow performance-wise.

Runing performance monitor while the query runs, I am showing the max
receive bandwith on my ethernet connection, so it appears that Access
is trying to download the entire table A before doing the join.

Is there a way to force Access to not download all of table A? If I
had to, I could resort to a VB loop that repeated a query 1000 times
for each row on table B, but is something easier?

I tried using "where tableA.KEY in (select num from TableB)", and tried
setting the field as a unique value when linking the view. Neither of
those options worked.

-MVL

Jun 28 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The simplest way may be to upload your thousand-record table to a table in
DB2, and perform the join in the SQL you use for the retrieval. You may need
permissions on the DB2 database that you currently do not have, so be
prepared to go "hat in hand" to the DBA of that database asking "how can I?"

What you see is perfectly normal... if one of the table in a join is a local
Jet table, Jet will need to download the server table to perform the join.

Yes, you could cycle through your thousand-record table, creating a Query
for each value, or for small groups of the values, which would retrieve just
one or a few records from the huge DB2 table -- these Queries would contain
the value, but would not reference the local table. While possibly not as
efficient as the first approach I suggested, it would almost certainly
outperform an attempt to download 6 million records to do the join locally.

Larry Linson
Microsoft Access MVP
<mv*************@yahoo.com> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...
I am trying to look up data on about 1000 records from a 6 million
record view on a DB2 database using msaccess.

The 6 million record DB2 view is connected through a linked ODBC table
(table A), and the 1000 primary key numbers I want are in a local
msaccess table (table B).

The most obvious solution is to join table A and B, but this is
excruciatingly slow performance-wise.

Runing performance monitor while the query runs, I am showing the max
receive bandwith on my ethernet connection, so it appears that Access
is trying to download the entire table A before doing the join.

Is there a way to force Access to not download all of table A? If I
had to, I could resort to a VB loop that repeated a query 1000 times
for each row on table B, but is something easier?

I tried using "where tableA.KEY in (select num from TableB)", and tried
setting the field as a unique value when linking the view. Neither of
those options worked.

-MVL

Jun 29 '06 #2

P: n/a
On Thu, 29 Jun 2006 04:58:55 GMT, "Larry Linson" <bo*****@localhost.not> wrote:
What you see is perfectly normal... if one of the table in a join is a local
Jet table, Jet will need to download the server table to perform the join.


Is this true? Surely not the whole table if the fields in the join are indexed in the server table.
Jun 29 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.