isladogs
MVP / VIP
- Local time
- Today, 03:18
- Joined
- Jan 14, 2017
- Messages
- 18,209
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
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
Last edited: