Mythbusters Poll - Query vs SQL vs Query Def (1 Viewer)

Which is fastest? Saved query/SQL/Query Def

  • db.Execute SQL

    Votes: 1 16.7%
  • DoCmd.RunSQL SQL

    Votes: 0 0.0%
  • db.Execute QueryName

    Votes: 2 33.3%
  • DoCmd.OpenQuery QueryName

    Votes: 0 0.0%
  • QueryDef.Execute

    Votes: 1 16.7%
  • DoCmd.OpenQuery QueryDef

    Votes: 0 0.0%
  • No significant difference

    Votes: 2 33.3%

  • Total voters
    6
  • Poll closed .

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
Preamble:
Like many here, 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

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 and it may well be the case that it depends on the nature of the query (or equivalent).

Here’s one 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

-----------------------------------------------

I’ve just recycled my speed comparison test app for yet another set of tests.
This time, I’m comparing the time to add 1 million records to a table in 100 loops of 10000 records appended from a source table
There are 6 tests as shown in the screenshot below



As you can see I am also comparing db.Execute with DoCmd for each method
There is a difference in speeds between the methods. Although relatively small in my tests, it is consistent

I will publish the results in a few days but before I do, I thought I’d ask what others think the answers are.

If you are willing to stick your head over the parapet, please indicate your opinion in the poll.
Many thanks
 

Attachments

  • MainForm.PNG
    MainForm.PNG
    47.9 KB · Views: 663
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 12:54
Joined
Feb 19, 2013
Messages
16,553
think you need to add another option to the poll - no significant difference.
 

sonic8

AWF VIP
Local time
Today, 13:54
Joined
Oct 27, 2015
Messages
998
I'm sligthly confused about these two:

db.Execute QueryDef
DoCmd.OpenQuery QueryDef
They both should result in a type mismatch error.


What about QueryDef.Execute?


I think your comparison scenario is not ideal. The approach I believe will perform best, will probably set itself apart more distinctively with increasing complexity of the source query.
 

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
Good point - now added option 7 & no longer a public poll (that was a mistake)

For info, there is about 10% difference between the slowest and fastest.
I've run each test 20 times and compacted each time before repeating.

The DoCmd versions are either all faster or all slower than db.Execute
And one of the 3 methods is both the fastest and the slowest of all (if that makes sense) which is perhaps surprising.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
Hi Phillip

Oops. The screenshot was test 5 was indeed misleading
I've just corrected the button caption - it is indeed QueryDef.Execute & altered the poll text

Code:
Test 5
 'populate table
    strSQL = "INSERT INTO tblData ( a ) SELECT tblSource.a FROM tblSource;"
    
    ' Create temporary QueryDef object.
    Set qdf = db.CreateQueryDef("", strSQL)
    
    For Q = 1 To LC 'set up loop
        qdf.Execute dbFailOnError
    Next Q

Test 6 was correct and I can assure you it does work

Code:
Test 6
'populate table
    strSQL = "INSERT INTO tblData ( a ) SELECT tblSource.a FROM tblSource;"
    
    ' Create temporary QueryDef object.
    Set qdf = db.CreateQueryDef("MyQueryDef", strSQL)
    
    For Q = 1 To LC 'set up loop
        DoCmd.OpenQuery "MyQueryDef"
    Next Q

I'm quite happy to accept that other queries may show any differences more effectively. All suggestions welcomed
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 28, 2001
Messages
26,996
Of the ones you listed, I would have thought that a DoCmd.RunSQL (of an SQL string) would be slowest. In theory, if it does a DoCmd.RunSQL of anything, it should be slower than anything not using DoCmd.

I would have said that db.Execute SQL and db.Execute QueryName would be close to each other and faster than the DoCmd variants.

I would have predicted that the QueryDef.Execute would have been fastest only because the query plan is already defined and ready for use when dealing with a defined query (as opposed to a raw SQL string).
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:54
Joined
Jan 20, 2009
Messages
12,849
I would expect the methods of the database to be slightly faster than DoCmd which is a method of the Application. Application methods must be interpreted then passed to the database.

We don't know what optimisation strategies are being used by Access. It might be reusing a query plan when it is running the same ad hoc query repeatedly. In SQL Server, query plans for ad hoc queries are stored in a cache for the term of the session or until the cache is full when it begins recycling. There is a setting that can be used cache space for the most often used queries by delaying storing plans for ad hoc queries until the second time they run.
 

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
Hi Galaxiom

Additional info that may help

Because the 6 tests each add 1,000,000 records after deleting those already present, it grows from about 1.3MB to 180MB after running all 6 tests. As a result, I have been compacting and closing the database between 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 app also 'randomly' varies the test order when running all tests.

Doc
Thanks for your thoughts on this. I'll publish in a few days.
Hopefully more people will reply/vote before then
 

sonic8

AWF VIP
Local time
Today, 13:54
Joined
Oct 27, 2015
Messages
998
The DoCmd-Object is a kludge around the basic DAO database functionality to make the functionality accessible in the Access-UI. So, I guess it will introduce a noticeable performance overhead.

My initial thoughts beyond that, were along the lines The_Doc_Man wrote. Any of the options using a stored query should perform better than ad-hoc SQL because the query plan would be reused. This effect should become more significant the more complex the source query becomes. The query in the comparison demo is so simple, there are hardly any different possible execution strategies available. So, I thought, you should have used a more complex query there, involving several joins, complex criteria, aggregation, and possibly a subquery.

However, this should become particularly noticeable in the comparison scenario repeating one and the same query thousands of times. This is hardly a process you would encounter in most real-world-application. So, the whole setup is mostly academic in nature and bears little relevance to day-to-day development work.

Whatever the results of this comparison, it should not be the main factor in your 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 – any – 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 does not happen only on the client for a single user, but on the database server for all users of your application.
 

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
Hi Phillip
Many thanks for your detailed reply
Below is a partial response to the comments made.
I've deliberately not commented on some points you raised as I'm delaying supplying the results to give others a chance to participate

The DoCmd-Object is a kludge around the basic DAO database functionality to make the functionality accessible in the Access-UI. So, I guess it will introduce a noticeable performance overhead.

Correct – as already stated by Galaxiom and to save further speculation, all DoCmd versions increase the time

However, as expected there is some disagreement about which of the 3 methods are fastest/slowest using the Execute approach. Investigating this was the main purpose of this thread / these tests

My initial thoughts beyond that, were along the lines The_Doc_Man wrote. Any of the options using a stored query should perform better than ad-hoc SQL because the query plan would be reused. This effect should become more significant the more complex the source query becomes. The query in the comparison demo is so simple, there are hardly any different possible execution strategies available. So, I thought, you should have used a more complex query there, involving several joins, complex criteria, aggregation, and possibly a subquery.

However, this should become particularly noticeable in the comparison scenario repeating one and the same query thousands of times. This is hardly a process you would encounter in most real-world-application. So, the whole setup is mostly academic in nature and bears little relevance to day-to-day development work.

I agree the query was very simple and therefore the differences relatively minor.
I am partly limited by wanting to make this file small enough that I can upload it for others to use.
That’s partly why the source table has 10K records that I loop through 100 times rather than having the full 1M records in the source

I have now modified each test to include an update ‘query’ on all 1M records after the looping has completed. Depending on the test, this adds about 60-100% to each test time.
So the differences are significantly greater with the fastest method over 25% quicker than the slowest …but the rank order for speed is unchanged (based on 5 tests).

I may add extra complexity before I publish later in the week (or leave that for others)

Whatever the results of this comparison, it should not be the main factor in your 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.

That is indeed the main reason I have continued to use SQL statements within code for the past 15 years or so (as mentioned in post 1).
For the same reason, I NEVER use embedded macros

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.

Indeed for some developers that may be the deciding factor.

This is not a too big factor in pure Access development. However, when you develop client-server-applications with a – any – 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 does not happen only on the client for a single user, but on the database server for all users of your application.

It is I think well known that optimising how data is extracted from a SQL Server BE (or equivalent) can have a significant effect on the speed of execution in the Access FE. I had considered doing similar speed tests with SQL Server but I’m not sure these will show anything unexpected.
__________________
 

isladogs

MVP / VIP
Local time
Today, 12:54
Joined
Jan 14, 2017
Messages
18,186
Over the past couple of weeks, I have done a variety of speed tests comparing saved queries with SQL statements and query defs. Each of these were run both using Execute & DoCmd giving 6 different methods



Each of these tests were run 20 times and average results calculated
I decided to test on several different setups:
a) Append only
b) Append + simple Update
c) Append + update from lookup table
d) Update from lookup table only
e) Append + both updates from b) & c)
f) Multiple action procedure (3 make table, 1 append, 3 update & 3 delete tables) using linked SQL tables
g) As above but using linked Access tables

The main trends from all the above tests are that:
1. In almost all cases, using Execute was faster than DoCmd as expected (though with some exceptions)
The overall speed variation between the 6 methods varied from 22% in test b) to just 3% in test d)
2. For simple queries (tests a & b) using db.Execute strSQL is the fastest method
3. For more complex queries (tests c,d,e), 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 (tests f & g), executing a SQL statement may once again be fastest though once again the differences are very small.
5. Executing query definitions is always slower than the other two methods
6. The slowest method was DoCmd.RunSQL strSQL in tests a,b,c,d,e and DoCmd.OpenQuery QueryDef for tests f & g
7. All tests done using linked Access tables were around 20% slower than the same tests using linked SQL tables. No attempt was made to optimise the SQL server tables compared to the Access equivalent

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.

It seems that the more complex the task being run, the smaller the variation in results

Taking all the tests together, the overall average results were as follows with only 5% variation in results and less than 0.5% difference between the 3 execute methods



I do realise that combining 7 different sets of test results isn’t in any way scientific

If anyone is interested, the full details of each test together with the results are available in a long article on my website http://www.mendipdatasystems.co.uk/speed-comparison-tests-6/4594478795
The same article also includes all the test files used if anyone wants to download them

My overall conclusion is that speed isn’t the most important factor in determining the best method to use
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 (sonic8) made much the same points in more detail in post 9
 

Attachments

  • SpeedTest8-MainMenu.PNG
    SpeedTest8-MainMenu.PNG
    61.1 KB · Views: 553
  • AVERAGETestResults8A-8G.PNG
    AVERAGETestResults8A-8G.PNG
    17.2 KB · Views: 531

Users who are viewing this thread

Top Bottom