Ms-access ODBC calls for linked table behaviour

ms accessodbcperformance

I'm curious if anyone knows how ms-access goes about generating ODBC calls for linked tables.

The reason I ask is that I'm trying to make the front-end of a system super efficient and too minimise the number of ODBC calls. I'm using MySql and have been performing some tests and looking at the general log from the MySql server. I was surprised at how many calls were made by the front-end.

a very simple example

Create the following test data in MySQL:

CREATE TABLE table1 (table1_id int(11) NOT NULL,
    column1 int(11) DEFAULT NULL,
    column2 int(11) DEFAULT NULL,
    column3 int(11) DEFAULT NULL,
    PRIMARY KEY (table1_id)
) ENGINE=InnoDB;

INSERT INTO sales.table1 (table1_id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

Now create a linked table in ms-access to table1 and open it. This is what appears in the general log file:

2011-05-29 16:58:00, SELECT `table1`.`table1_id` FROM `table1`
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 1  
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 1 OR `table1_id` = 2 OR `table1_id` = 3 OR `table1_id` = 4 OR `table1_id` = 5 OR `table1_id` = 6 OR `table1_id` = 7 OR `table1_id` = 8 OR `table1_id` = 9 OR `table1_id` = 10
2011-05-29 16:58:00, SELECT `table1_id`,`column1`,`column2`,`column3`  FROM `table1`  WHERE `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11 OR `table1_id` = 11

Rather than retrieve all the data in one hit which I could do with a stored procedure it seems to require 4 steps:

1) get a list of the primary keys from table1
2) Request the 1st 10 records by explicitly selecting them in the where clause.
3) Retrieve the next 10 records by explicitly selecting them in the where clause.
4) Because the 1st record has the focus upon opening the table, requery the 1st record again

Notice the odd behavior in step 3 where the criteria table1_id=11 is repeated 10 times.

Best Answer

The only way to get complete control over the query for ODBC is to create them as pass-through queries.

In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).

In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).

So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.

On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.

You need to test both and choose what works best depending on the exact query and how it is used.

Related Question