Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Preamble:

Like many developers, when I started using Access some 20 years ago, I used queries and macros before moving across to using SQL statements in code.

 

When I made the change, I was informed how much more efficient it was to run SQL in code. At some point after that, I read that was nonsense and that queries run faster because they are pre-optimised.

 

And, just to complicate things further, others said using query definitions was better than either of those. Those expressing opinions for each view included experienced developers some of whom were MVPs

 

Faced with this disparity, I long ago decided to continue mainly using what worked best for me – SQL statements in code though others will have their own preferences

 

Here’s one forum thread discussing the different approaches and with differing opinions: https://social.msdn.microsoft.com/Forums/office/en-US/3a26a941-b75b-49e4-bfe8-10c152f2b6c0/sql-or-querydef-in-vba-code?forum=accessdev

 

Of course, with modern processors, the question is much less important than when I started using Access. However, I’ve never yet read a definitive answer to the question.

I am well aware that the best approach may depend on the nature of the query (or equivalent).

 

In an attempt to get a definitive answer, I have recycled my speed comparison test application for  another set of tests. This time, I compared the time to add 1 million records to a data table in 100 loops of 10000 records appended from a source table and then to run update queries.

 

The tests compare the times using SQL statements, saved queries and query definitions

For each method, I also compared the use of db.Execute with DoCmd therefore giving 6 tests in all.

 

The app also 'randomly' varies the test order when running all tests.

Speed Comparison Tests                                                  Page 6 (of 8)

Screenshots

Click any image to view a larger version ...

1 2 3 Return To Top Page 6 of 8 Return to Access Articles 4

The 6 tests each add 1,000,000 records after deleting those already present. The database grows from about 1.3MB to 180MB after running all 6 tests. As a result, I compacted and reopened the database after each test.

 

I believe compacting forces Access to redo any saved query plan.

I have also tried running individual tests several times in a row to see what effect that has.

 

The reason for appending records in repeated loops is partly to reduce the size of the test database. However, it also means than Access can optimise the query execution plans as appropriate for each method.

 

In the end I ran several variations on this test for reasons explained below.

Each test was run 20 times and average times calculated

5 SpeedTest8-MainMenu

a)  Append ONLY

In the first set of tests, the times to append the 1,000,000 records were compared

Although the difference was relatively small in my tests, it was very consistent in repeated testing.

 

Using db.Execute was ALWAYS faster than DoCmd.

DoCmd is an application method which needs to be interpreted before it can be run

 

The slowest time was about 11% greater than the fastest time

However there was only about a 3% difference between the 3 execute methods

 

For these tests, running a SQL statement was fastest followed by QueryDef with a saved query slowest.

However for the DoCmd version, using a SQL statement is by some distance the slowest.

TestResults8A

b)  Append + Update

The first set of tests were very simple and it could easily be argued that this wasn’t a typical everyday situation.

 

So I decided to follow up by running a simple update on the entire data table of 1 million records.

 

The update was also very simple just changing the value of all records in a text field to a different value

 

The relative order was the same as in the first set of tests but there was a much larger difference between the average times with the slowest times almost 22% more than the fastest.

 

However the results fell into two distinct groups with only 1.6% difference in the 3 execute methods

TestResults8B TestResults8C

c)  Append + Update from Lookup table

Next, I decided to run a more complex update query where the text field value was updated to one of 10 possible values from a lookup table. Inevitably this significantly increased the time for each of the tests and I anticipated the differences in times would be further increased.

 

However, that wasn’t the case.

The differences between the fastest and slowest times were reduced to about 10%

Also, for the first time, running a saved query was faster than running a SQL statement

d)  Update from Lookup table

For obvious reasons, I decided to repeat the previous test but this time only timing the update part.

 

The saved query name was again fastest but there was only a 3% overall variation between the results.

An unexpected result was that for query definitions, using DoCmd was faster than Execute

TestResults8D TestResults8E

f)  Multiple Action Procedure

In this example, both sets of update query were included in the results.

 

Saved queries were again fastest with query definitions slightly faster than using a SQL statement

However there was only 7% overall variation in the results and around 0.7% between the 3 execute methods

e)  Append & both Updates

Finally I decided to run a procedure from one of my production databases with a SQL Server BE.

The procedure included reference to 8 separate SQL tables.

 

In the original procedure, a SQL table GradeAverages is updated after each school reporting session.

As I didn’t want to modify the ‘real’ data, I modified the procedure to update a local table instead.

The modified procure included 3 make table queries (to create temp tables), 1 append query and 3 update queries followed by 3 statements to delete the temp tables

 

Each procedure was run 10 times in a loop in order to optimise the query execution plan for each test.

There was less than 5% variation between the different tests and only 1.3% between the execute methods.

 

I would have expected greater variation between the different methods.

 

However, despite the relative ‘complexity’ of the procedure, using executing SQL statements was easily the fastest method.

 

TestResults8G TestResults8F AVERAGETestResults8A-8G

So that I could upload this version of the test, I copied all 8 SQL tables used to a separate Access BE file.

No changes were made to the table structures, indexes etc.

 

For my own interest, I ran the same tests again.

The overall order was almost unchanged but all times were about 20% slower than using the SQL BE file.

This result was somewhat unexpected and may merit a separate investigation.

 

Until now, I had only ever compared performance before & after upsizing to SQL.

 

Many users are disappointed by the performance when converting tables to a SQL BE.

 

The reality for many is that performance after upsizing is often slower than before. In order to improve performance it is necessary to make changes which utilise how SQL Server retrieves data.

 

That may form the basis of a separate article in the future

Conclusions

Clearly it isn’t possible to make sweeping conclusions from all of these tests.

 

As each of the 7 sets of tests were performed the same number of times, it might be interesting to view the overall average times by each method.

Notice that the order is reversed for the 3 tests using DoCmd compared to Execute

 

There is less than 5% variation between the 6 methods overall in these 140 tests on each.

Furthermore, there is less than 0.5% difference between the 3 execute methods

 

Of course, this isn’t scientific as each of the 7 versions are performing different actions.

 

However the main trends from all the above test are that:

 

1.  Using Execute methods are almost always faster than using DoCmd. In certain cases up to 20%

    faster though usually much less than that

 

2.  For simple queries, dbExecute strSQL is generally fastest and DoCmd.RunSQL strSQL is slowest

 

3.  For more complex queries, executing a saved query is usually fastest as Access can optimise the

    query execution plan. However the variation between the different tests is often smaller.

 

4.  For procedures involving a number of action queries, executing a SQL statement may once again

    be fastest though once again the differences are very small.

 

5.  Executing query definitions appears to never be the fastest method in these tests

 

6.  Executing queries based on SQL tables may be faster than doing the same queries on linked

    Access tables

 

Overall, however, in most cases, the differences aren’t that significant and other factors may be more important in determining the best method to use.

 

for example, if you prefer keeping all code in one place to allow an overview and easy editing, using SQL statements may be best. Conversely if you wish to keep as much as possible separate from VBA code, using saved queries works well

 

 

Phillip Stiefel has expressed the same points in more detail:

 

The results of this comparison should not be the main factor in choosing one approach over any other.

 

More important in my opinion, is something not related to performance. If you use ad-hoc SQL in your VBA-Code the SQL’s logic is visible right there in the code. You might find this more convenient because it saves you the context switch of looking at the query when trying to comprehend the whole logic of a particular piece of your application.

 

On the other hand, using stored queries and referencing those in the code will decouple the query logic from the calling code. This allows you to change query logic without touching the code at all. Having this separation might be beneficial when maintaining the application.

 

This is not a too big factor in pure Access development. However, when you develop client-server-applications with a SQL-Server-Backend, this can be huge benefit. Then you are able to change a lot of logic by modifying views and stored procedures on the server without ever touching and re-deploying the code in the frontend application. This forms a real abstraction layer between the data storage (tables) and the front-end-application. A huge benefit for long-term maintenance of an application.

 

Additionally, the compilation of queries and reuse of query plans becomes a much bigger factor if it does not happen only on the client for a single user, but on the database server for all users of your application.

 

6

Click to download:    Speed Comparison tests 8.2a - Append         Approx 1.5 MB (zipped)

Click to download:    Speed Comparison tests 8.2b - Append + Update         Approx 1.5 MB (zipped)

Click to download:    Speed Comparison tests 8.2c - Append + Update from Lookup Table         Approx 1.5 MB (zipped)                                                                                              

Click to download:    Speed Comparison tests 8.2d - Update from Lookup Table         Approx 1.5 MB (zipped)                                                                                              

Click to download:    Speed Comparison tests 8.2e - Append + both Updates         Approx 1.5 MB (zipped)                                                                                              

Click to download:    Speed Comparison tests 8.2g - Multiple Action Procedure         Approx 34 MB (zipped)                                                                                              

SpeedTest8-Chart SpeedTest8-results

6.    Query vs SQL vs Query Def                              Updated 27/02/2019

7 8

To provide feedback on this article, please enter your name, e-mail address and company (optional) below, add your comment and press the submit button. Thanks

* Required