ODBC linked tables vs. SQL specific passthrough

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 10 Jul 2006 16:18 GMT

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.

Reply to this Message

Jeff L - 10 Jul 2006 18:21 GMT

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.

Reply to this Message

Brian - 10 Jul 2006 19:55 GMT

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.

> 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
[quoted text clipped - 8 lines]
> data, but when you are using tables with 1000's of records you will
> certainly notice a difference.

Reply to this Message

Jeff L - 10 Jul 2006 20:54 GMT

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.

Reply to this Message

Rick Brandt - 13 Jul 2006 04:54 GMT

> 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
[quoted text clipped - 3 lines]
> 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.

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message