An interesting experiment! Need others opinion on different ODBC query behaviors. (1 Viewer)

B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
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:

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:
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
Doh-

Forgot one more request- If anyone can do a similar experiment on MS SQL, I would be so *much* obliged.

Thank you.
 
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
I guess it's old news and I'm the last one to find out? :confused:
 
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
Anybody care to clue me in?
 
G

georgedwilkinson

AWF VIP
Local time
Today, 12:06
Joined
Mar 4, 2008
Messages
3,856
I haven't had time to try it out on SQL. Got a few fires to put out.

I'll try it next week, if that's soon enough? I'll PM you when I need information on the experiment.
 
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
Oh, no hurry! It's not a do-or-die situation here! (If it was, I'd be offering you a million dollars and 72 virgins girls, wouldn't I be? But all I can promise you is a warm'n'fuzzy feeling of satisfaction. :D )

If this isn't particularly sensitive data, no need for PM so everyone can benefit and replicate this for themselves. :)

Thanks so much for volunteering to be my Pinky!

Together, we shall achieve world domination! <evil laughter>
 
D

datAdrenaline

AWF VIP
Local time
Today, 12:06
Joined
Jun 23, 2008
Messages
697
Ok ... for starters ... I am not at my machine with SQL Server and the Profiler on it, so ... this is from memory from past experiments and some BRIEF reference reading (there is a link below) ... and I have 7 kids ... so ... you can take that as you wish, but for the most part, I beleive the following is accurate.

------

Something to remember ... ODBC and Access does some magic with CACHING ... so although what you have seen actually happened, it does not mean one is more efficient than the other simply because the caching mechanisms may be different as ODBC and Access communicate with the data. For example, if I set my RecordSource to the linked table ...

tblAreas

JET sends the SQL statement to ODBC, ODBC converts and sends the statement to SQL Server, then ODBC creates an SP on SQL Server that uses an argument that has the same datatype as the PK, then ODBC executes the SP with a PK value to get the data back to JET. But if I let the form "rest" a bit, SQL Server activity continues and the SP is executed over and over again, populating the form with the entire recordset ... so the recordset is populating Asynchronously, but none-the-less consuming processing time on the local PC and SQL Server, but in the background ... cool eh? ... when I navigate, there is no activity since the record is already cached.

Note the same happens when I use a SQL statement like

SELECT * FROM tblAreas

as a record source or a query, the same things happens ...

Now ... when I filter my source to only return one record ... like this ...

SELECT * FROM tblAreas WHERE AreaID = 5

All the same stuff happens except no caching, so the local PC and SQL Server are freed up to process other stuff.

Now ... if I use the little known SQL Syntax ...

SELECT * FROM
[ODBC;DRIVER=SQL Server;
SERVER=T02ADN01;
UID=xxxx;
PWD=xxxx;
DATABASE=xxxx;
Network=DBMSSOCN].tblAreas

{Which is about the same as an IN clause, accept you have to ability to JOIN tables from completely different sources ... which I think is really cool to do when trying to sync databases.}

Guess what ... the SQL Profiler sees the same thing as before! ... go figure :rolleyes: ... however, I *think* Access may have a little slower response with this syntax, possibly because it has to figure out the connection stuff, then once it does, it jumps on to an existing ODBC connection or creates another one.

With a PASSTHRU query with an SQL statement that looks like this:

SELECT * FROM tblAreas WHERE AreaID = 5

The profiler sees 1 thing ... the SQL statement! thats it!

Now with an SQL statement like this (still in passthrough) ...

SELECT * FROM tblAreas

The profiler still sees 1 thing, the request for data! ... I would think caching is happening on the local PC, but I don't know since unfiltered PASSTHRU can bog things down if your source is 3 million rows!. The lesson from the PASSTHRU for me is to limit (filter) your request for data!

If you want ... you can click here for a similar post that refutes the myth that the "entire table" passes through the network when using linked tables. The post I linked you to was done while viewing the profiler, so if a discrepancy exists between the text in this post and the linked post, the linked post is probably more accurate :eek:.

So ... to be brief (hah!) ... I think the ODBC drivers and caching have A LOT to do with it ... but from what I've seen, ODBC is pretty smart!

Hope that helps!!
 
Last edited:
G

georgedwilkinson

AWF VIP
Local time
Today, 12:06
Joined
Mar 4, 2008
Messages
3,856
Banana said:
Thanks so much for volunteering to be my Pinky!

Together, we shall achieve world domination! <evil laughter>
Click to expand...

Your Pinky? Hah, that's the genius of my plan...making you think the roles are reversed.

Have you noticed all the attention we're getting from the heavy hitters? Man, this is nice.

Thanks for the excellent info, Mr. Adrenalin! It's neat to be able to have discourse about deeper programming stuff! Too bad I'm not smart enough to...hey, what's that? A fly?
 
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
Brent, this is useful information.

From the initial glance, it seems to verify that this is more Jet's doing and not necessary the implementation of each ODBC drivers (as I wasn't quite sure whether the drivers themselves are simply thin wrappers of the formal ODBC implementation, or provided more than just translating ODBC calls into servers' native calls.)

Now that you made a point about that I'm probably just seeing different way of it being cached, I will make a note to check how much work MySQL did in either scenarios. It's just that it's a bit strange that in the first two trials with queries referencing linked tables, we would have extra SELECTS that weren't really needed, even a full-table scan. (To be fair, though, I suspect that my logs does not tell whether a query of "SELECT * FROM foo;" was executed asynchronously and didn't necessarily hammer the MySQL. Will have to research into this.)

It's just possible that simply by specifying ODBC connection, I was able to get better results for the traffic between Access and MySQL because of what you described about ODBC's caching and creating a SP on MS SQL.
 
D

datAdrenaline

AWF VIP
Local time
Today, 12:06
Joined
Jun 23, 2008
Messages
697
Hello Bannana ...

{Yep ... Leigh was correct ... It does feel a little weird saying that! ...}

Do take note that several hits will hit the server if you go to design veiw, change the source, then switch to form view. When you switch to Design view, the schema needs to be read, then when you change the source, the schema is read again, then when you switch to Form view, the data is read.... so when you are testing, be sure to just start your log from the opening the form ...

... I realize you may have already done that, so, please don't interpret this post as anything else but a reminder/fyi or whatever ...:rolleyes:
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:06
Joined
Feb 19, 2002
Messages
43,266
You've done a great job with Brent but one thing you might want to do to level the playing field is to set the query with the linked tables to be not updateable. Jet knows that the Pass-Thru query is not updateable and it handles read-only recordsets differently than it handles updateable recordsets (test the exact same query as the recordsource for a report and for a form to see the difference). You should see a reduction in the "noise" that always accompanies an updateable query.
 
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
datAdrenaline said:
Hello Bannana ...

{Yep ... Leigh was correct ... It does feel a little weird saying that! ...}
Click to expand...

Just don't tell your wife you talked to a banana. You don't need to go to the funny farm. Not yet, anyway! :)

Do take note that several hits will hit the server if you go to design veiw, change the source, then switch to form view. When you switch to Design view, the schema needs to be read, then when you change the source, the schema is read again, then when you switch to Form view, the data is read.... so when you are testing, be sure to just start your log from the opening the form ...

... I realize you may have already done that, so, please don't interpret this post as anything else but a reminder/fyi or whatever ...:rolleyes:
Click to expand...

Yes, that is very important reminder. When I did the experiment in my OP, I closed the form completely before each trials, then marked my log so I knew where it started.
 
B

Banana

split with a cherry atop.
Local time
Today, 10:06
Joined
Sep 1, 2005
Messages
6,318
Pat Hartman said:
You've done a great job with Brent but one thing you might want to do to level the playing field is to set the query with the linked tables to be not updateable. Jet knows that the Pass-Thru query is not updateable and it handles read-only recordsets differently than it handles updateable recordsets (test the exact same query as the recordsource for a report and for a form to see the difference). You should see a reduction in the "noise" that always accompanies an updateable query.
Click to expand...

Now, I didn't realize that Jet will behave differently with a read-only recordset. I probably should re-check my combobox and make sure it's read-only because 90% of the time, we don't really need a dynaset for rowsources. (This reminds me, I really need to check about whether NotInList event can still add row to a combobox with a read-only recordset...)
 
You must log in or register to reply here.

Similar threads

B
Can someone explain to me why Jet requires a full table scan from ODBC?
Replies
0
Views
1,374
Banana
B
B
Jet not playing nice with ODBC and Server
Replies
4
Views
838
CraigDolphin
CraigDolphin
S
I get an error on my form because my query keeps on changing
Replies
16
Views
2,401
Wiz47
Wiz47
K
Need Help with Query by Form Code
Replies
2
Views
1,158
cogent1
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom