This is same post with some editing I posted on usenet. I'd like to post here in hopes someone may have an answer.
I'm trying to understand how Jet behaves with ODBC sources. I've logged the SQL received by the server and while I think that Jet acts generally intelligently when we want to navigate a bound form or update/insert/delete a record. It is a good citizen in those scenarios, only selecting few rows for the navigation, and using WHERE clause to guarantee that it works with one or few rows at a time.
However, when I open a form (in this scenario, a simple bound form with a simple linked subform), I get several queries, which seems to be redundant.
The form's recordsouce:
Main form's recordsource: SELECT * FROM country;
Subform's recordsource: SELECT * FROM city;
Linked Fields:
(Master) Code = (Child) CountryCode
Specific questions:
1) Why ask for a full table scans even if it already has the keys it needs? Indeed, why ask for it multiple times? The whitepaper on Jet & ODBC says that Jet will ask for 'SHOW TABLE' among other informational SQL when it loads the linked tables, so I'm not concerned about that.
2) Putting in WHERE values does seems to help in limiting the recordset, but Jet still will try to retrieve the whole recordset. Why can't Jet just fetch the first few rows?
3) Can I improve the behavior somehow? Is it possible to bind a form to a whole table (of course using query), which logically mean no WHERE clause. Can this still be done without requiring a full table scan?
I could of course write some VBA routines to dynamically limit the recordset, but wanted to make sure I wasn't over complicating anything.
4) Microsoft's white paper on Jet's behaviors with ODBC ( Jet and ODBC Connectivity[url], for version 3.0, but I understand this still is valid for 4.0) mentions that when a dynaset is used, Jet will only retrieve the unique identifying columns, which I can see it doing when the database opens up and repairs its connection string. But this isn't being done when a form is opened. Furthermore, it's supposed to fetch only 100 rows, but I see nothing like this in SQL log. Why?
I'm trying to understand how Jet behaves with ODBC sources. I've logged the SQL received by the server and while I think that Jet acts generally intelligently when we want to navigate a bound form or update/insert/delete a record. It is a good citizen in those scenarios, only selecting few rows for the navigation, and using WHERE clause to guarantee that it works with one or few rows at a time.
However, when I open a form (in this scenario, a simple bound form with a simple linked subform), I get several queries, which seems to be redundant.
The form's recordsouce:
Main form's recordsource: SELECT * FROM country;
Subform's recordsource: SELECT * FROM city;
Linked Fields:
(Master) Code = (Child) CountryCode
Code:
071115 10:56:56 78 Connect root@localhost on world
78 Query SET SQL_AUTO_IS_NULL = 0
78 Query SELECT Config, nValue FROM MSysConf
78 Query SELECT `Code` ,`Name` ,`Continent` ,`Region` ,`SurfaceArea` , `IndepYear` ,`Population` ,`LifeExpectancy` ,`GNP`, `GNPOld` ,`LocalName` , `GovernmentForm` ,`HeadOfState` ,`Capital` ,`Code2` FROM `country`
78 Query SELECT `ID` ,`Name` ,`CountryCode` ,`District` ,`Population` FROM `city`
78 Query SELECT `ID` ,`Name` ,`CountryCode` ,`District` ,`Population` FROM `city`
78 Query SELECT `country`.`Code` FROM `country`
78 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`, `IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`, `GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE `Code` = 'ABW'
78 Query SELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
78 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
78 Query SELECT `country`.`Code` FROM `country`
78 Query SELECT `city`.`ID` FROM `city` WHERE ( '' = `CountryCode` )
78 Query SELECT `city`.`ID` FROM `city` WHERE ( '' = `CountryCode` )
78 Query SELECT `Code`,`Name`,`Continent`,`Region`,`SurfaceArea`, `IndepYear`,`Population`,`LifeExpectancy`,`GNP`,`GNPOld`,`LocalName`, `GovernmentForm`,`HeadOfState`,`Capital`,`Code2` FROM `country` WHERE `Code` = 'ABW'
78 Query SELECT `city`.`ID` FROM `city` WHERE ( 'ABW' = `CountryCode` )
78 Query SELECT `ID`,`Name`,`CountryCode`,`District`,`Population` FROM `city` WHERE `ID` = 129Specific questions:
1) Why ask for a full table scans even if it already has the keys it needs? Indeed, why ask for it multiple times? The whitepaper on Jet & ODBC says that Jet will ask for 'SHOW TABLE' among other informational SQL when it loads the linked tables, so I'm not concerned about that.
2) Putting in WHERE values does seems to help in limiting the recordset, but Jet still will try to retrieve the whole recordset. Why can't Jet just fetch the first few rows?
3) Can I improve the behavior somehow? Is it possible to bind a form to a whole table (of course using query), which logically mean no WHERE clause. Can this still be done without requiring a full table scan?
I could of course write some VBA routines to dynamically limit the recordset, but wanted to make sure I wasn't over complicating anything.
4) Microsoft's white paper on Jet's behaviors with ODBC ( Jet and ODBC Connectivity[url], for version 3.0, but I understand this still is valid for 4.0) mentions that when a dynaset is used, Jet will only retrieve the unique identifying columns, which I can see it doing when the database opens up and repairs its connection string. But this isn't being done when a form is opened. Furthermore, it's supposed to fetch only 100 rows, but I see nothing like this in SQL log. Why?
