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: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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, Ive 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).

Heres one thread discussing the different approaches and with differing opinions: https://social.msdn.microsoft.com/Fo...orum=accessdev

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

Ive just recycled my speed comparison test app for yet another set of tests.
This time, Im 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 Id 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

__________________
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
Previously known as ridders : 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:19 PM.
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,439
Thanks: 40
Thanked 3,368 Times in 3,263 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: 122
Thanks: 23
Thanked 37 Times in 36 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 Video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, recorded at the AEK conference, Nuremberg, Oct. 2018.
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: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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
Previously known as ridders : 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: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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
Previously known as ridders : 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: 12,472
Thanks: 62
Thanked 1,175 Times in 1,075 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,291
Thanks: 78
Thanked 1,409 Times in 1,329 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
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 online now   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: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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
Previously known as ridders : 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: 122
Thanks: 23
Thanked 37 Times in 36 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 SQLs 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 Video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, recorded at the AEK conference, Nuremberg, Oct. 2018.
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: 6,980
Thanks: 92
Thanked 1,715 Times in 1,592 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
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
Previously known as ridders : 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: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Mythbusters Poll - Query vs SQL vs Query Def

I normally use db.execute query

__________________
After 20 years working with access i have no more hair to give.
MickJav 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 - Speed Comparison Tests - Having vs Where isladogs Code Repository 16 11-04-2018 07:27 AM
Mythbusters - Make Table vs Append Query theDBguy Code Repository 4 11-03-2018 02:25 PM
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 07:46 PM.


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

Sponsored Links

How to advertise

Media Kit


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