Re: HOWTO?: force optimizer to use a "remote index join" when joining local to remote table
- From: <david@epsomdotcomdotau>
- Date: Mon, 25 Jun 2007 21:48:51 +1000
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)
"Malcolm Cook" <malcook@xxxxxxxxxxxxxxxx> wrote in message
news:OFCMISdsHHA.484@xxxxxxxxxxxxxxxxxxxxxxx
They are against a mysql instance running over the net on hardware I don'tcontrol and mirroring it to run the test you're
suggesting (monitoring the actual query sent to the server) is costprohibitive.
to use the "remote index join". I can tell by monitoring
I really just need to know if there is a way to FORCE the query optimizer
the performance and the memory usage that it is NOT happening in aspecific case, despite the fact that my local table is "small" (1
row) and my remote table is "large" (many hundreds of thousands).wrote in message
???
Malcolm
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
news:%23dpG3XcsHHA.1728@xxxxxxxxxxxxxxxxxxxxxxxreally happening on the server side by using the
If your queries are against SQL-Server, it should be easy to see what's
news:epE84O5rHHA.4932@xxxxxxxxxxxxxxxxxxxxxxxSQL-Server Profiler. For Oracle, you will find a similar tool.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Malcolm Cook" <malcook@xxxxxxxxxxxxxxxx> wrote in message
index join", and I do WANT it to be performed, but I am sureI am finding cases where I would expect Access to perform a "remote
optimizer, or otherwise cause this query strategy to beit is NOT being performed.
What I want to know is if there is a way to provide a hint to the query
using ODBC data sources" -taken.
I learned about this by reading "How to optimize Microsoft Access when
remote tables in different ODBC data sources. Joinshttp://support.microsoft.com/kb/286222 , where it states
"Heterogeneous joins between local tables and remote tables, or between
column is indexed, may result in a remote index join. In abetween small local tables and large remote tables, where the join
the server, and only the joining rows are returned."remote index join, one query for each row in the local table is sent to
Thanks,
Malcolm
.
- References:
- HOWTO?: force optimizer to use a "remote index join" when joining local to remote table
- From: Malcolm Cook
- Re: HOWTO?: force optimizer to use a "remote index join" when joining local to remote table
- From: Sylvain Lafontaine
- Re: HOWTO?: force optimizer to use a "remote index join" when joining local to remote table
- From: Malcolm Cook
- HOWTO?: force optimizer to use a "remote index join" when joining local to remote table
- Prev by Date: Re: transfertext specifications
- Next by Date: Re: Missed data export from XL to Access via ADO
- Previous by thread: Re: HOWTO?: force optimizer to use a "remote index join" when joining local to remote table
- Next by thread: Terminal Services
- Index(es):
Relevant Pages
|
