Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-29-2016, 03:09 AM   #1
Lee Mac
Newly Registered User
 
Join Date: Sep 2016
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Lee Mac is on a distinguished road
DoCmd.OpenQuery vs DoCmd.RunSQL vs OpenRecordset

I'm in the process of designing an application which will source data from 5 separate tables (each subject to selection criteria), combine fields from each table, and finally append the records to another table.

Since each of the source tables contains a large amount of data (on the order of 7,500,000 records), I'm taking some time to consider the most efficient way of tackling the task and so am requesting advice from those with more experience.

Below are the various methods I've considered:

1. DoCmd.OpenQuery

Since the selection criteria applies to fields in several tables, I have found that there is a performance gain in building Access Queries which select a subset of records from a single 'root' table, and then using this as a subquery joined with other tables before applying further selection criteria (as opposed to building a single query in which all tables are joined and all selection criteria is applied).

After building the nested queries, I could then simply call DoCmd.OpenQuery from my application.

Aside: when evaluating a query, does the database engine link all of the data in the tables prior to evaluating the selection criteria, or apply the selection criteria to the 'parent' table before linking?

2. DoCmd.RunSQL

As above, however, I would construct the queries in SQL and call the DoCmd.RunSQL method. However, if structuring the query using multiple subqueries, I'm concerned about a limit on the nesting levels afforded by SQL in Access; furthermore, if there is no performance gain compared with DoCmd.OpenQuery, the queries would be more maintainable if built in Access.

Recordsets (DAO)

Where the appending operation is concerned, is it more efficient to use an INSERT INTO statement in SQL, an Append Action Query in Access, or iterate over a Recordset using the AddNew/Update methods?

Many thanks in advance for your time.

Lee

Lee Mac is offline   Reply With Quote
Old 11-29-2016, 05:18 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,391
Thanks: 40
Thanked 3,687 Times in 3,551 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: DoCmd.OpenQuery vs DoCmd.RunSQL vs OpenRecordset

the one to avoid is

Quote:
iterate over a Recordset using the AddNew/Update methods
It is the slowest by far - perhaps a factor of 100 x slower.

the difference between openquery and runsql is that openquery can open a select query or an action query, whilst runsql can only run action sql (i.e. not reference a query object).

you also have the execute method for executing SQL action queries. primary difference to runsql is the error messages and outcome messages (i.e. '20 records appended'). execute has more control

with regards efficiency when designing your queries, ensure your tables are properly indexed and avoid subqueries as much as possible (and definitely avoid domain functions). On the volumes you are talking about they will kill performance.

Performance wise, I've never done any testing to see which is most efficient, but my guess would be for each method being pretty much the same for the same query - openquery perhaps having a very small advantage because the query will already have been compiled and the queryplan developed - but at the most a few seconds.

It really comes down to how you want to report progress
__________________
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-29-2016, 05:29 AM   #3
tyworld
Newly Registered User
 
Join Date: Nov 2016
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
tyworld is on a distinguished road
I do a similar process of appending then INSERT INTO and I use DoCmd. openQuery with no issues.


Sent from my iPhone using Tapatalk

tyworld is offline   Reply With Quote
Reply

Tags
efficiency , performance , query , recordset , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
DoCmd.OpenQuery scrappy2 Modules & VBA 2 12-16-2008 09:05 AM
Using DoCmd.OpenQuery in VB6.0 Fugative Visual Basic 1 10-09-2006 07:36 PM
DoCmd.OpenQuery ??? ccflyer Modules & VBA 7 08-19-2005 06:59 PM
[SOLVED] help with docmd.openquery skoogie Modules & VBA 2 10-06-2003 04:38 AM
Docmd.OpenQuery VBAhole22 Modules & VBA 8 08-05-2003 11:15 AM




All times are GMT -8. The time now is 06:21 AM.


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 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World