While trying to optimize my query performance with a ODBC backend, I noticed something very interesting.
We probably already know that a pass-through query gives best performance, but with none of updatability or "intelligence" of Jet selectively retrieving few records at all, and that a carelessly written query referencing linked tables can hammer the server.
But here's what I get when I change my query to specify ODBC source rather than linked table sources- former is much more conservative and performs better than the latter, even if the query itself is 100% identical.
Experiment:
Backend server: MySQL 5.1
Database: World database (a popular learning database among MySQL folks), listing all countries and major cities- 38 countries and 4079 cities. The server is remote and connected across the Internet (e.g. WAN).
ODBC Driver: MyODBC 5.1
Front-end interface: Access 2003, with all tables linked in Access 2000 format.
Interface: A form listing information about a Country, with a combobox showing the Capital of the country, and a subform listing all cities within that country, so we have a total of three queries.
What I did: Open the form, move one record then closed it.
Results
For those queries referencing the linked tables:
This is what MySQL sent back:
Trial #1
A total of 10 SELECTS just to open the form and move one record.
Trial #2
11 SELECT statement this time. Note the that first SELECT statement pulls all records for the countries in North America continent.
Now constrast this to those queries:
Trial #1
Only 6 SELECTs.
Trial #2:
Still 6 SELECTs, which is just right since we have three queries in use by the interface, and we only navigated one record.
Keep in mind that when the form is opened for the first time in a connection, we usually have a bunch of commands like "SHOW KEYS FROM City" and/or "SHOW TABLE STATUS LIKE 'Country'" which Jet needs to have in order to navigate/update the records. I believe this is cached as long as the connection stays alive with periodical updates.
Mind you, those are *NOT* pass-through queries. They're essentially the same queries, with the properties Source Connect Str set:
I don't know if it's old news to MVPs here, but I sure as hell didn't know about that difference, whether this is Jet's doing or ODBC driver's doing, and what ramifications it holds for queries' performance.
Thoughts?
We probably already know that a pass-through query gives best performance, but with none of updatability or "intelligence" of Jet selectively retrieving few records at all, and that a carelessly written query referencing linked tables can hammer the server.
But here's what I get when I change my query to specify ODBC source rather than linked table sources- former is much more conservative and performs better than the latter, even if the query itself is 100% identical.
Experiment:
Backend server: MySQL 5.1
Database: World database (a popular learning database among MySQL folks), listing all countries and major cities- 38 countries and 4079 cities. The server is remote and connected across the Internet (e.g. WAN).
ODBC Driver: MyODBC 5.1
Front-end interface: Access 2003, with all tables linked in Access 2000 format.
Interface: A form listing information about a Country, with a combobox showing the Capital of the country, and a subform listing all cities within that country, so we have a total of three queries.
What I did: Open the form, move one record then closed it.
Results
For those queries referencing the linked tables:
Code:
[PLAIN]Main Form: SELECT Country.Code, Country.Name, Country.Continent, Country.Region, Country.SurfaceArea, Country.IndepYear, Country.Population, Country.LifeExpectancy, Country.GNP, Country.GNPOld, Country.LocalName, Country.GovernmentForm, Country.HeadOfState, Country.Capital, Country.Code2 FROM Country WHERE (((Country.Continent)='North America'));
Sub Form: SELECT City.ID, City.Name, City.CountryCode, City.District, City.Population FROM City WHERE (((City.CountryCode)=[Forms]![frmCountry]![Code]));
Combo Box: SELECT City.ID, City.Name FROM City WHERE (((City.CountryCode)=[Forms]![frmCountry]![Code]));[/PLAIN]
This is what MySQL sent back:
Trial #1
Code:
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ABW'
91 Query SELECT `City`.`ID` FROM `City` WHERE ((`CountryCode` = 'ABW' ) AND ( 'ABW' = `CountryCode` ) )
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR 'ID' = 129
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ABW'
91 Query SELECT `ID` ,`Name` FROM `City` WHERE (`CountryCode` = 'ABW' )
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'AIA'
91 Query SELECT `City`.`ID` FROM `City` WHERE ((`CountryCode` = 'AIA' ) AND ( 'AIA' = `CountryCode` ) )
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 61
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ANT' OR `Code` = _latin1'ATG' OR `Code` = _latin1'BHS' OR `Code` = _latin1'BLZ' OR `Code` = _latin1'BMU' OR `Code` = _latin1'BRB' OR `Code` = _latin1'CAN' OR `Code` = _latin1'CRI' OR `Code` = _latin1'CUB' OR `Code` = _latin1'CYM'
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62
A total of 10 SELECTS just to open the form and move one record.
Trial #2
Code:
91 Query SELECT `Country`.`Code` FROM `Country` WHERE (`Continent` = 'North America' )
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ABW'
91 Query SELECT `City`.`ID` FROM `City` WHERE ((`CountryCode` = 'ABW' ) AND ( 'ABW' = `CountryCode` ) )
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129 OR `ID` = 129
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ABW'
91 Query SELECT `ID` ,`Name` FROM `City` WHERE (`CountryCode` = 'ABW' )
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'AIA'
91 Query SELECT `City`.`ID` FROM `City` WHERE ((`CountryCode` = 'AIA' ) AND ( 'AIA' = `CountryCode` ) )
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 61
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ANT' OR `Code` = _latin1'ATG' OR `Code` = _latin1'BHS' OR `Code` = _latin1'BLZ' OR `Code` = _latin1'BMU' OR `Code` = _latin1'BRB' OR `Code` = _latin1'CAN' OR `Code` = _latin1'CRI' OR `Code` = _latin1'CUB' OR `Code` = _latin1'CYM'
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62
11 SELECT statement this time. Note the that first SELECT statement pulls all records for the countries in North America continent.
Now constrast this to those queries:
Code:
[PLAIN]Main Form: SELECT Country.Code, Country.Name, Country.Continent, Country.Region, Country.SurfaceArea, Country.IndepYear, Country.Population, Country.LifeExpectancy, Country.GNP, Country.GNPOld, Country.LocalName, Country.GovernmentForm, Country.HeadOfState, Country.Capital, Country.Code2 FROM Country IN '' [ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=world;] WHERE (((Country.Continent)='North America'));
Sub Form: SELECT City.ID, City.Name, City.CountryCode, City.District, City.Population FROM City IN '' [ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=world;] WHERE (((City.CountryCode)=[Forms]![frmCountry]![Code]));
Combo Box: SELECT City.ID, City.Name FROM City IN '' [ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=world;] WHERE (((City.CountryCode)=[Forms]![frmCountry]![Code]));[/PLAIN]
Trial #1
Code:
91 Query SELECT `ID` ,`Name` FROM `City` WHERE (`CountryCode` = 'ABW' )
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'AIA'
91 Query SELECT `City`.`ID` FROM `City` WHERE ((`CountryCode` = 'AIA' ) AND ( 'AIA' = `CountryCode` ) )
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 61
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ANT' OR `Code` = _latin1'ATG' OR `Code` = _latin1'BHS' OR `Code` = _latin1'BLZ' OR `Code` = _latin1'BMU' OR `Code` =_latin1'BRB' OR `Code` = _latin1'CAN' OR `Code` = _latin1'CRI' OR `Code` = _latin1'CUB' OR `Code` = _latin1'CYM'
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62
Only 6 SELECTs.
Trial #2:
Code:
91 Query SELECT `ID` ,`Name` FROM `City` WHERE (`CountryCode` = 'ABW' )
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'AIA'
91 Query SELECT `City`.`ID` FROM `City` WHERE ((`CountryCode` = 'AIA' ) AND ( 'AIA' = `CountryCode` ) )
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 61
91 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`,`IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`,`GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `Country` WHERE `Code` = _latin1'ANT' OR `Code` = _latin1'ATG' OR `Code` = _latin1'BHS' OR `Code` = _latin1'BLZ' OR `Code` = _latin1'BMU' OR `Code` = _latin1'BRB' OR `Code` = _latin1'CAN' OR `Code` = _latin1'CRI' OR `Code` = _latin1'CUB' OR `Code` = _latin1'CYM'
91 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `City` WHERE `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62 OR `ID` = 62
Still 6 SELECTs, which is just right since we have three queries in use by the interface, and we only navigated one record.
Keep in mind that when the form is opened for the first time in a connection, we usually have a bunch of commands like "SHOW KEYS FROM City" and/or "SHOW TABLE STATUS LIKE 'Country'" which Jet needs to have in order to navigate/update the records. I believe this is cached as long as the connection stays alive with periodical updates.
Mind you, those are *NOT* pass-through queries. They're essentially the same queries, with the properties Source Connect Str set:
I don't know if it's old news to MVPs here, but I sure as hell didn't know about that difference, whether this is Jet's doing or ODBC driver's doing, and what ramifications it holds for queries' performance.
Thoughts?
Last edited: