DoCmd.OpenQuery vs DoCmd.RunSQL vs OpenRecordset (1 Viewer)

Lee Mac

New member
Joined
Sep 28, 2016
Messages
8
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
 

CJ_London

Super Moderator
Staff member
Joined
Feb 19, 2013
Messages
11,580
the one to avoid is

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
 

tyworld

New member
Joined
Nov 28, 2016
Messages
1
I do a similar process of appending then INSERT INTO and I use DoCmd. openQuery with no issues.


Sent from my iPhone using Tapatalk
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom