Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
View Poll Results: Which is fastest? Saved query/SQL/Query Def
db.Execute SQL 1 16.67%
DoCmd.RunSQL SQL 0 0%
db.Execute QueryName 2 33.33%
DoCmd.OpenQuery QueryName 0 0%
QueryDef.Execute 1 16.67%
DoCmd.OpenQuery QueryDef 0 0%
No significant difference 2 33.33%
Multiple Choice Poll. Voters: 6. You may not vote on this poll

Reply
 
Thread Tools Rate Thread Display Modes
Old 11-10-2018, 02:05 PM   #1
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,644
Thanks: 102
Thanked 2,285 Times in 2,103 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Mythbusters Poll - Query vs SQL vs Query Def

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/Fo...orum=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
Attached Images
File Type: png MainForm.PNG (47.9 KB, 72 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-23-2018 at 09:58 AM. Reason: Added missing image
isladogs is offline   Reply With Quote
Old 11-10-2018, 03:02 PM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,685
Thanks: 40
Thanked 3,465 Times in 3,354 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Mythbusters Poll - Query vs SQL vs Query Def

think you need to add another option to the poll - no significant difference.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 11-10-2018, 03:02 PM   #3
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 157
Thanks: 29
Thanked 48 Times in 46 Posts
sonic8 is on a distinguished road
Re: Mythbusters Poll - Query vs SQL vs Query Def

I'm sligthly confused about these two:

Quote:
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.

__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Old 11-10-2018, 03:11 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,644
Thanks: 102
Thanked 2,285 Times in 2,103 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Mythbusters Poll - Query vs SQL vs Query Def

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.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-10-2018 at 03:40 PM.
isladogs is offline   Reply With Quote
Old 11-10-2018, 03:25 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,644
Thanks: 102
Thanked 2,285 Times in 2,103 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Mythbusters Poll - Query vs SQL vs Query Def

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 11-10-2018 at 03:39 PM.
isladogs is offline   Reply With Quote
Old 11-10-2018, 09:40 PM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,136
Thanks: 70
Thanked 1,377 Times in 1,269 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Mythbusters Poll - Query vs SQL vs Query Def

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).
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 11-11-2018, 01:04 AM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,405
Thanks: 84
Thanked 1,433 Times in 1,352 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Mythbusters Poll - Query vs SQL vs Query Def

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.

Galaxiom is offline   Reply With Quote
Old 11-11-2018, 01:14 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,644
Thanks: 102
Thanked 2,285 Times in 2,103 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Mythbusters Poll - Query vs SQL vs Query Def

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-11-2018, 03:39 AM   #9
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 157
Thanks: 29
Thanked 48 Times in 46 Posts
sonic8 is on a distinguished road
Re: Mythbusters Poll - Query vs SQL vs Query Def

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.
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Old 11-11-2018, 04:29 AM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,644
Thanks: 102
Thanked 2,285 Times in 2,103 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Mythbusters Poll - Query vs SQL vs Query Def

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

Quote:
Originally Posted by sonic8 View Post
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

Quote:
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)

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

Quote:
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.

Quote:
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.
__________________
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-12-2018, 03:05 PM   #11
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 736
Thanks: 37
Thanked 29 Times in 24 Posts
MickJav is on a distinguished road
Re: Mythbusters Poll - Query vs SQL vs Query Def

I normally use db.execute query
__________________
Free to use Contact Manager:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Just Added DD's Simple Home Accounts:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Available by request only through Database Dreams site:
Project Manager:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
Old 11-28-2018, 10:49 AM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 8,644
Thanks: 102
Thanked 2,285 Times in 2,103 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Mythbusters Poll - Query vs SQL vs Query Def

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/s...s-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
Attached Images
File Type: png SpeedTest8-MainMenu.PNG (61.1 KB, 52 views)
File Type: png AVERAGETestResults8A-8G.PNG (17.2 KB, 54 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
query defs , saved queries , speed test , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mythbusters - Make Table vs Append Query theDBguy Code Repository 6 03-23-2019 02:55 PM
Mythbusters - Speed Comparison Tests - Having vs Where isladogs Code Repository 16 11-04-2018 07:27 AM
Blue's Age poll. BlueIshDan The Watercooler 68 05-15-2015 02:20 AM
[SOLVED] How do you create a poll? BlueIshDan The Watercooler 16 10-23-2014 08:45 AM
Exit Poll KenHigg The Watercooler 5 11-22-2008 08:20 PM




All times are GMT -8. The time now is 12:35 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World