HOWTO?: force optimizer to use a "remote index join" when joining local to remote table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Malcolm Cook - 15 Jun 2007 22:25 GMT

I am finding cases where I would expect Access to perform a "remote index join", and I do WANT it to be performed, but I am sure it
is NOT being performed.

What I want to know is if there is a way to provide a hint to the query optimizer, or otherwise cause this query strategy to be
taken.

I learned about this by reading "How to optimize Microsoft Access when using ODBC data sources" -
http://support.microsoft.com/kb/286222 , where it states

"Heterogeneous joins between local tables and remote tables, or between remote tables in different ODBC data sources. Joins between
small local tables and large remote tables, where the join column is indexed, may result in a remote index join. In a remote index
join, one query for each row in the local table is sent to the server, and only the joining rows are returned."

Thanks,

Malcolm

Reply to this Message

Sylvain Lafontaine - 18 Jun 2007 17:29 GMT

If your queries are against SQL-Server, it should be easy to see what's
really happening on the server side by using the SQL-Server Profiler. For
Oracle, you will find a similar tool.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

>I am finding cases where I would expect Access to perform a "remote index
>join", and I do WANT it to be performed, but I am sure it is NOT being
[quoted text clipped - 17 lines]
>
> Malcolm

Reply to this Message

Malcolm Cook - 18 Jun 2007 19:14 GMT

They are against a mysql instance running over the net on hardware I don't control and mirroring it to run the test you're
suggesting (monitoring the actual query sent to the server) is cost prohibitive.

I really just need to know if there is a way to FORCE the query optimizer to use the "remote index join". I can tell by monitoring
the performance and the memory usage that it is NOT happening in a specific case, despite the fact that my local table is "small" (1
row) and my remote table is "large" (many hundreds of thousands).

???

Malcolm

> If your queries are against SQL-Server, it should be easy to see what's really happening on the server side by using the
> SQL-Server Profiler. For Oracle, you will find a similar tool.
[quoted text clipped - 15 lines]
>>
>> Malcolm

Reply to this Message

Sylvain Lafontaine - 19 Jun 2007 06:56 GMT

But the problem here is that JET doesn't know when the remote table is large
or not when it is working against a server. When querying against another
JET database, JET can and will make use of the indexes to determine the size
of the remote table or to get an approximation of the number of rows that
might get involved in the JOIN and act accordingly but when working against
a SQL-Server or a MySQL database, it's impossible for JET to get access to
this kind of information.

Did you notice that in the article that you have mentionned in your original
post, there is the little phrase « Functionality *not supported* by servers
in general includes the following: » just before the mention about remote
index join?

This article doesn't tell you that JET can perform a remote index join
between a small local table and a larget remote table when working against a
SQL-Server or any other servers like MySQL; in fact, it tells you exactly
the opposite, ie. that it *cannot* do such a thing.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> They are against a mysql instance running over the net on hardware I don't
> control and mirroring it to run the test you're suggesting (monitoring the
[quoted text clipped - 35 lines]
>>>
>>> Malcolm

Reply to this Message

Malcolm Cook - 22 Jun 2007 17:50 GMT

Sylvain,

Yeah, I read all that, but the scoping of what is and is not supported is not clear (to me at least). And, anyway, it is clear to
me that access is capable sending "one query for each row in the local table [] to the server, and only the joining rows are
returned.". It is really just a matter whether Access allows forcing this implementation of joining a local table with a remote
table (i.e. via some sort of 'hint', perhaps). It appears I'm "outta luck", and need to "roll my own" in this regard. Not a
problem.

Thanks for following along....

--Malcolm

> But the problem here is that JET doesn't know when the remote table is large or not when it is working against a server. When
> querying against another JET database, JET can and will make use of the indexes to determine the size of the remote table or to
[quoted text clipped - 38 lines]
>>>>
>>>> Malcolm

Reply to this Message

david@epsomdotcomdotau - 25 Jun 2007 12:45 GMT

If you are using ODBC, you can turn on ODBC tracing
on your workstation - no change to the server at all - to
see the ODBC messages going from Jet to the ODBC driver.
This will work for any ODBC driver or client, since the service
is provided by the ODBC framework, not the driver or client.

ODBC messages take the form of SQL statements. The
syntax is similar to MS SQL Server SQL.

Logging to disk will slow ODBC. Be sure to turn it off
when you are finished.

There is no way to provide 'hints' to Jet.

However, Jet implements actual ODBC SQL (it doesn't cheat
and assume anything about what the driver will accept), which
means that it can only send one Left or Right join per ODBC
statement. For this reason, multi-table queries are faster if they
use only inner joins, and multiple left/right joins are faster if you
use OLEDB. (faster if your server is lightly loaded. Remote joins
load the server, not the workstation)

If your query really is returning only a few lines, make it a snapshot
instead of a dynamic curser. Use another form to do updates and
appends.

(david)

> They are against a mysql instance running over the net on hardware I don't control and mirroring it to run the test you're
> suggesting (monitoring the actual query sent to the server) is cost prohibitive.
[quoted text clipped - 26 lines]
> >>
> >> Malcolm

Reply to this Message

A a r o n K e m p f - 18 Jun 2007 18:36 GMT

you need A c c e s s D a t a P r o j e c t s

>I am finding cases where I would expect Access to perform a "remote index
>join", and I do WANT it to be performed, but I am sure it is NOT being
[quoted text clipped - 17 lines]
>
> Malcolm

Reply to this Message