My
question is - is it more efficient to just use sql pass through queries
than
using linked ODBC tables? In a quick test, the query seemed to return
data
like greased lightning (just something like "Select * from ...").
huh? Pass through to what? Are you talking about using a pass through query
to a oracle database farm with 20 machines, each with 4 processors, and
sharing all the data. Then, yes..using a pass through query makes sense,
since then you eliminate the "jet" database engine from the picture, and the
RAW SQL text is sent to the server based database engine. That huge
fantastic high speed database engine can then munch and crunch on your
sql...and EVENTUALLY return some results.
Think of pass-though like when you submit a search to Google....
However, if you are talking about a JET BASED system, then why would odbc,
or a pass through query make any difference at all? I mean, pass though to
what?
When you use a JET file share, you are OPENING a windows file. Just like
you open word, or excel. If you place the file on your local hard drive, or
a network share, you are still opening the file. The ODBC connection is
really to JET, which in terms must resolve to FULL WINDOWS PATH NAME to a
file. So, you can use odbc to connect to your JET software on your computer,
but then that JET will open the file....
If you eliminate ODBC...you are eliminating some possible overhead..but,
processing is insignificant in this case.
So, a pass through query to JET means really nothing in this case. What are
you passing through the sql to? The pass-threw query is ONLY going to be of
some advantage if you have a sql server engine, or some processing "running"
in which you can pass the query to..and then have a process "wait" for the
data to come back.
With a JET file share to a mdb file...you are really just opening a standard
windows file. There is no socket connection that occurs here. Hence, you
can't connect to a mdb file share over the internet for example. You can't
use odbc to a jet file share over the internet. However, you can use
ms-access and odbc over the internet to sql server for example. This is
because you have a "engine" on the other end that can accept that
pass-through sql, and process it for you, and then return some results back.
With ms-access, and JET, you are talking about a standard windows file open.
Thus, there is NO ability to use jet and odbc over the internet. You don't
have anything to pass through the sql too!!! You have a local copy of JET
running, and that is it. that jet to grab data does NOT communicate with
something...but simply opens a standard windows file. Really, no different
then
open a word file. We can't use the term "pass-through" to open a word file.
It is just a simple file sitting on the disk.
The sql query is being executing by code running local on your computer.
There is no sending of the sql to "SOME SERVER" that can execute the sql.
So, really, I can't see any difference in performance would occur here at
all...
A file is a file is a file........and a jet mdb share is simply a windows
file that gets open...
ms-access --->jet----->c:\my documents\customers.mdb
or
ODBC --->jet------>c:\my documents\customers.mdb
if have sql server on the other end, then
ODBC --->jet------>super duper high speed oracle database
if you use a pass though query, then
ODBC --->----->super duper high speed oracle database
So, a pass-through is faster in the above, since we by-pass jet. In many
cases, by-passing jet will NOT make a difference. however, if you have sql
that joins data from two tables, then jet might start requesting data from
that oracle server, and from both tables..and THEN do the join. IF you send
a pass-thought to oracle, then the oracle database will do the join of the
data, AND THEN RETURN the results.
So, pass-through query is ONLY going to be of any real use when you have a
true database server, and only then will it be of use if JET is going to do
a rotten job of requesting data from that oracle (or whatever server based
system you are using).....
The very concept of pass-through, and a jet file share don't make any sense
at all in the context of a jet mdb file share. The whole concpet, or idea
does not even apply.....
