How Jet Engine Optimizes between linked (remote?) tables?

[Guest]

Hello,

I am new ACCESS user and have some questions.

1. What kinds of joins (eg. next loop join, merge join etc..) Jet Engine
provide? and could someone give me some links for the information?

2. Is Jet Engine a CBO? Could you link some pages?

3. Is there a way to see the execution plan of Jet Engine?

4. When back-end database file is located in a server (but Access database
is not installed on the server), how Jet Engine behaves if front-end database
(ACCESS installed on client computer) accesses to back-end database file on
the server? Does Jet Engine (in client computer) first retrieve data and
perform actions on the client side?

Thanks in advance

Daniel Yang

 

[Armen Stein]

Hello,

I am new ACCESS user and have some questions.

1. What kinds of joins (eg. next loop join, merge join etc..) Jet Engine
provide? and could someone give me some links for the information?

JET provides all the normal joins: inner, left outer, right outer,
cartesian product, union, etc. It will also do unequal joins,
although I don't see this often in practice. Some joins cannot be
represented using the visual designer; you must use SQL view.

2. Is Jet Engine a CBO? Could you link some pages?

I'm not familiar with the term CBO.

3. Is there a way to see the execution plan of Jet Engine?

Yes, using JET's SHOWPLAN. Here's just one of many articles about it:
http://articles.techrepublic.com.com/5100-22-5064388.html

4. When back-end database file is located in a server (but Access database
is not installed on the server), how Jet Engine behaves if front-end database
(ACCESS installed on client computer) accesses to back-end database file on
the server? Does Jet Engine (in client computer) first retrieve data and
perform actions on the client side?

When the FE and BE are on different computers, all work is done on the
FE computer. The BE computer basically acts as another hard drive.

This arrangement is suitable for some applications on fast networks.
For more performance, SQL Server may be used as a back-end database,
which allows server-side operations.

Thanks in advance

Daniel Yang

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jamie Collins]

JET provides all the normal joins: inner, left outer, right outer,
cartesian product, union, etc. It will also do unequal joins,
although I don't see this often in practice. Some joins cannot be
represented using the visual designer; you must use SQL view.

Access/Jet does not support UNION JOIN. I'm sure it is not missed,
either.

One common and useful JOIN type that is not supported in Access/Jet is
FULL OUTER JOIN. Actually, its LEFT/RIGHT OUTER JOIN syntax is not
ANSI-92 compliant and works in a rather odd way (see
http://support.microsoft.com/kb/208880).

While Access/Jet doesn't explicitly support the in-line CROSS JOIN
syntax, the 'old style' 'Cartesian product' syntax works fine;
similarly, there are some predicates which Access/Jet does not like in
the ON clause but work perfectly well in the WHERE clause (same
problem for LEFT/RIGHT OUTER JOIN but this time the trick won't work).

Jamie.

--