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