ODBC linked tables vs. SQL specific passthrough

[Guest]

I have several apps that use ODBC linked tables to various back ends (SQL,
DB2, ProvideX), and all of them could benefit from any efficiency I can
provide. Someone told me I could speed these up significantly by using
SQL-specific passthrough queries instead of linked tables.

Is this true? If so, can someone please (briefly) explain the advantage or
post a link to some documentation on the comparison.

TIA.

 

[Jeff L]

Yes it is true. I have found that running pass-through queries speed
up the process significantly. For example, one query I was using took
about 30 seconds to run when using the ODBC linked tables and running a
query on them. When I used a pass-through query instead, it took about
2 seconds. The way I understand it is Access goes out to the server
and grabs the entire table you are querying on and "downloads" it into
Access, and then runs the query and spits out the results. When a
pass-through query is run, the command is sent to the server and just
the query's results are sent back to Access. You probably would not
notice much of a difference when using tables that don't have much
data, but when you are using tables with 1000's of records you will
certainly notice a difference.

 

[Guest]

Thanks. Real-world experience is the best proof.

Do you think this would also be true of a file-based DB back-end? The first
app I need to update is one that extracts data from Sage's MAS90 which uses
ProvideX. Since it is not a client-server DB configuration and is instead
file-based, is there any reason to expect any performance enhancement?
Anything will help, but I don't really want to waste my time on this one if
there will not be any gain.

 

[Jeff L]

I have only used MS SQL Server and am not familiar with what you are
using so unfortunately I cannot shed any useful information on that.

 

[Rick Brandt]

Yes it is true. I have found that running pass-through queries speed
up the process significantly. For example, one query I was using took
about 30 seconds to run when using the ODBC linked tables and running
a query on them. When I used a pass-through query instead, it took
about 2 seconds. The way I understand it is Access goes out to the
server and grabs the entire table you are querying on and "downloads"
it into Access, and then runs the query and spits out the results.

While it is "possible" to construct a query against an ODBC link where the above
processing would happen it is not going to happen that way every time or even
most of the time. In fact the vast majority of the query processing against a
link is passed to the server for processing.