Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
I'd come to the same conclusions myself! I'm using pass-through queries where-ever possible now to gain the control and reduce the 'chatter' as you put it. This has really improved performance for me when using a high latency network e.g. wireless router and broadband VPN connection –  David Jun 21 '11 at 16:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.