Queries and Linked tables to Database Server

[Nic]

Hello All!

I've got a performance question regarding how queries are executed in Access
(Access 2000) when all the tables are linked to a MySQL server.

If I have a Query, say:
"SELECT * From [tablename] WHERE [Columnname] = 1"

and the table "tablename" is a Linked table to a MySQL server (through
MyODBC 3.51, and is set as a DSN)

Now, does Access pull all the data from [tablename] off the server and then
evaluates the WHERE statement? Or does the query execute on the MySQL
server, and the data is sent to Access.

What about quries that call up sub quries?

Any one have any links maybe?

Thanks!
-Nic

 

[Rick Brandt]

Hello All!

I've got a performance question regarding how queries are executed in Access
(Access 2000) when all the tables are linked to a MySQL server.

If I have a Query, say:
"SELECT * From [tablename] WHERE [Columnname] = 1"

and the table "tablename" is a Linked table to a MySQL server (through
MyODBC 3.51, and is set as a DSN)

Now, does Access pull all the data from [tablename] off the server and then
evaluates the WHERE statement? Or does the query execute on the MySQL
server, and the data is sent to Access.

What about quries that call up sub quries?

The answer is "it depends". Access/Jet will (for the most part) get the
server to do the work providing there aren't lots of joins or heterogeneous
joins or WHERE clauses using functions, etc..

My advice is build the queries you need and don't worry about it as long as
they perform satisfactorily. You can use a PassThrough query or Stored
Procedure to force all processing to be done on the server so when in
doubt, build one of those and see if it runs any better.