Access & ODBC Data Access/Retrieval Performance Test Suite (1 Viewer)

Banana

split with a cherry atop.
Local time
Today, 03:33
Joined
Sep 1, 2005
Messages
6,318
Note: This is posted at UtterAccess as well.

This topic is nothing new, probably has been rehashed countless times with several people making assertion one way or other. For me, I decided to put several assumptions to test. They can range from "you should use query, not linked table as recordsource for form", "DSN-less is faster than DSN", or even old myth that never dies: "the whole table is pulled over the wire." However, the trouble (for me at least) was that I've had took some of assertions as gospel and didn't bother to test each one of them. I did test some of them, but not as fully as I think I should have had. Thus, I wanted to do some tests and organize results so assertions no longer are just assertions but rather something backed up by fact. (Well... even that word 'fact' is dubious at best. What is 'fact' to my computer may very well not be a 'fact' to your computer.... so take this with a caution) Hopefully by having some kind of empirical data, we can make a more informed decisions/assertions about what works best.

The reason why I wanted to do this was because many moons ago, I did some experiments ago on MySQL and discovered that if I used ODBC queries (no, not passthrough queries; just plain queries with property Connection String filled) I got better performance. I was kind of surprised by that result, and felt that it had to be a fluke, and thus made a mental note to test it further when I had time and with different backends. When I finally got a copy of Developer edition for MS-SQL (I suppose I could have tested on the express edition.... oh, well), I decided to take that old experiment up and do a bit more rigorous testing that should make my college physics teacher proud.

So, I built a test suite using two database I knew about, MySQL and MS-SQL, and used MySQL's World database, and adapted it to MS-SQL, so both had functionally equivalent databases. (To make it equivalent, I also changed the tables' engine from default MyISAM to InnoDB, so it's more akin MS-SQL's transactional engine as well).

I don't think I'm done with testing, as I can think up some more different things to test and probably needing to repeat some tests just to make sure I wasn't seeing another fluke.

In all case, we use same form with a subform (well, actually four near-identical copies of form and two near-identical copies of subforms), which represents countries and cities respectively of which there are 239 records and 4079 records. The test runs through different permutations of possible combinations, setting up the queries & saving it (to create an execution plan) and updating the forms' recordsource. The combinations are deployed as a bitwise flags, so there's a maximum of 128 possible combinations, but I only had 64 combinations as I excluded some aspects as explained below. The SQL statements (with exception for citeria) should be identical, with differences being only in which tables are used, and in what manner the table is accessed.

Parameters used:

Query
If toggled, it means a query is used. Otherwise, a table is used. In the test I did, I excluded any combinations where Query isn't flagged, as I hadn't quite made up my mind about how I wanted to use the tables directly with other parameters. I basically wanted to do a test upon table-type recordset (if that's applicable to ODBC, which I doubt it, but am not definitely sure to say so) performance compared to queries. Because it is always toggled, it's not displayed in results.

DSN
When enabled, a DSN connection is used. Otherwise, DSN-less.

ODBC
When enabled, the query's Connection String is filled and backend's actual tables are referenced. Otherwise, linked table object are referenced in the query.

Linked
When enabled, the subform control's property Link Master Filed & Link Child Field is filled. Otherwise, it is left blank. Whenever a unlinked subform is used, the subform's query is coereced to have a criteria as it would not make sense otherwise.

Criteria
When enabled, the main form's query will have a static WHERE clause selecting all countries where Continent = North America. Likewise, the subform's query will filter city based on Country Code via form referencing. (Note: Combobox's rowsource always has criteria which is same as subform's criteria)

Combobox
When enabled, the form with a combobox is used. This isn't strictly a data access test as it is obvious that an extra query will slow things down and shouldn't be compared to queries/forms without the combobox; the intention is primarily to study if rowsource may behave differently from recordsource or not. It should also be noted that the forms has a Requery command executed on the OnCurrent event to update the combobox's selection list, which is another expense that can add quite significantly to the overall time.


The scope of test

I want to make it clear from outset that this test is primarily interested in performance of accessing records from Access as a front-end client. This has no bearings whatsoever on the backend's performance (though I'd bet that ODBC drivers will play a important role in performance, though). The results displayed will be only representative of Access on my VM (and even then, there's no guarantee that what worked on my VM will work on other computer) The whole point is to determine under what conditions can we see best performance, and whether we should let Access do all the work or can we do a better job doing it ourselves? We also will only concern ourselves with working with data through forms as that IMHO, is where there's lot of mystery. With reports and/or bulk operations, it's less of an issue because we can just use passthrough query to extract every bit of efficiency out of extensions provided by the backend vendors.

Also, it is assumed that a updateable recordset is required. It goes without saying that using snapshot-type recordsets or passthrough queries or even unbound forms will have major ramifications on performance, but they are not considered in this scope. It should be of course noted that there has been postings scattered over the internet reporting better performance using "half-bound" forms where passthrough query is used as a recordsource, then flipped over to unbound form to allow editing or just doing it the nitty-gritty way with full on unbound forms. Likewise, this does not cover use of binding forms to a ADO (or perhaps even DAO) recordsets, which has its own set of ramifications.

The test is concerned with speed & efficiency of data retrieval, and attempts to indirectly test this by browsing each record on the screen (using RunCommand MoveToNextRecord), incurring expense of both retrieval and painting the rows on the screen. I rolled in the painting portion mainly because I believe it's a integral part of bound form- to browse without painting the screen may or may not be consistent so for that reason I made this a requirement.

The data are sorted by Avg, ascending.

Without a combobox
Code:
ODBC	DSN	Crit	Linked	DB	Run1	Run2	Run3	Avg	Dev
0	0	-1	-1	MS-SQL	0:49	1:01	0:49	0:53	0:10
0	-1	-1	-1	MS-SQL	1:02	0:55	0:46	0:54	0:11
0	0	-1	-1	MySQL	1:00	0:54	0:51	0:55	0:06
0	0	0	-1	MySQL	0:54	1:01	0:52	0:56	0:07
-1	-1	0	0	MySQL	0:59	0:59	0:51	0:56	0:07
0	0	-1	0	MySQL	1:02	0:51	0:54	0:56	0:08
0	-1	0	-1	MS-SQL	0:57	1:01	0:50	0:56	0:08
0	-1	0	-1	MySQL	0:54	1:03	0:51	0:56	0:09
0	0	-1	0	MS-SQL	1:00	1:00	0:49	0:56	0:09
-1	0	-1	0	MS-SQL	0:58	1:01	0:52	0:57	0:06
0	-1	0	0	MS-SQL	1:02	0:58	0:50	0:57	0:09
-1	0	0	0	MySQL	0:59	1:03	0:49	0:57	0:10
0	-1	0	0	MySQL	0:59	1:03	0:52	0:58	0:08
-1	-1	-1	0	MS-SQL	0:56	1:04	0:54	0:58	0:07
0	-1	-1	0	MS-SQL	1:02	1:01	0:54	0:59	0:06
0	0	0	0	MySQL	1:04	1:02	0:51	0:59	0:10
-1	0	-1	0	MySQL	1:00	1:05	0:53	0:59	0:09
0	-1	-1	-1	MySQL	1:04	1:01	0:52	0:59	0:09
0	-1	-1	0	MySQL	1:05	1:01	0:50	0:59	0:11
-1	-1	-1	0	MySQL	1:05	1:05	0:53	1:01	0:10
-1	0	-1	-1	MS-SQL	1:08	1:05	0:54	1:02	0:10
-1	-1	-1	-1	MS-SQL	1:03	1:08	0:56	1:02	0:09
0	0	0	0	MS-SQL	1:02	1:01	1:06	1:03	0:04
-1	-1	0	-1	MySQL	1:17	1:12	1:08	1:12	0:06
-1	0	0	-1	MySQL	1:07	1:20	1:08	1:12	0:10
-1	0	-1	-1	MySQL	1:23	1:20	1:03	1:15	0:15
-1	-1	-1	-1	MySQL	1:21	1:22	1:07	1:17	0:12
0	0	0	-1	MS-SQL	1:32	1:34	1:19	1:28	0:12
-1	-1	0	0	MS-SQL	1:31	1:30	1:25	1:29	0:05
-1	0	0	0	MS-SQL	1:28	1:36	1:23	1:29	0:09
-1	-1	0	-1	MS-SQL	1:34	1:37	1:30	1:34	0:05
-1	0	0	-1	MS-SQL	1:40	1:37	1:29	1:35	0:08



With a combobox
Code:
ODBC	DSN	Crit	Linked	DB	Run1	Run2	Run3	Avg	Dev
0	-1	0	-1	MySQL	1:20	1:15	1:05	1:13	0:11
0	-1	0	0	MS-SQL	1:15	1:18	1:10	1:14	0:06
0	0	-1	-1	MySQL	1:13	1:21	1:09	1:14	0:09
0	-1	0	-1	MS-SQL	1:19	1:16	1:11	1:15	0:06
0	0	0	-1	MySQL	1:21	1:19	1:04	1:15	0:13
-1	-1	0	0	MySQL	1:25	1:10	1:10	1:15	0:12
0	-1	0	0	MySQL	1:16	1:19	1:10	1:15	0:06
0	0	0	0	MySQL	1:19	1:21	1:09	1:16	0:09
0	-1	-1	0	MySQL	1:24	1:20	1:03	1:16	0:16
-1	0	-1	0	MySQL	1:13	1:24	1:10	1:16	0:10
0	0	-1	0	MySQL	1:17	1:20	1:10	1:16	0:07
0	-1	-1	-1	MySQL	1:22	1:23	1:07	1:17	0:13
0	-1	-1	0	MS-SQL	1:18	1:23	1:10	1:17	0:09
-1	-1	-1	0	MySQL	1:20	1:21	1:09	1:17	0:09
0	-1	-1	-1	MS-SQL	1:23	1:22	1:09	1:18	0:11
-1	0	0	0	MySQL	1:25	1:22	1:09	1:19	0:12
-1	0	0	-1	MySQL	1:37	1:37	1:23	1:32	0:11
-1	-1	-1	-1	MySQL	1:45	1:35	1:26	1:35	0:13
-1	-1	0	-1	MySQL	1:44	1:40	1:27	1:37	0:13
-1	0	-1	-1	MySQL	1:45	1:38	1:30	1:38	0:11
0	0	-1	0	MS-SQL	1:48	1:47	1:40	1:45	0:06
-1	-1	-1	0	MS-SQL	1:53	1:43	1:44	1:47	0:08
-1	0	-1	0	MS-SQL	1:52	1:49	1:41	1:47	0:08
0	0	-1	-1	MS-SQL	1:53	1:51	1:41	1:48	0:09
-1	0	-1	-1	MS-SQL	1:58	1:58	1:49	1:55	0:07
-1	-1	-1	-1	MS-SQL	1:53	2:01	1:52	1:55	0:07
0	0	0	0	MS-SQL	2:32	2:38	2:33	2:34	0:05
-1	-1	0	-1	MS-SQL	2:35	2:33	2:35	2:34	0:02
-1	0	0	0	MS-SQL	2:33	2:38	2:35	2:35	0:04
-1	-1	0	0	MS-SQL	2:41	2:36	2:32	2:36	0:06
-1	0	0	-1	MS-SQL	2:37	2:38	2:36	2:37	0:01
0	0	0	-1	MS-SQL	2:36	2:37	2:39	2:37	0:02


With uncertainty of +/- 1 second. I'll leave it up to the rest to interpret the results... I also look to any criticism on whether those are valid or not, and how I can improve.

Oh, yeah, here's my environment parameters, FWIW.
Access 2003 SP3 11.8166.8172
Windows XP Professional Version 2002 SP3
Intel Core Duo 2 @ 2.4 Ghz
1.5 GB of RAM w/PAE


I don't mind sharing my database upon request; I'm hoping that others can help provide feedback and ensure the validity of tests before I make it available for general download.
 

Users who are viewing this thread

Top Bottom