Stored Query vs SQL Statement

[ADezii]

One frequently asked question at TheScripts is "Should I use a Stored Query or an SQL Statement in those situations that require a Query (RecordSets, RecordSources, Append, Delete, Update Operations, etc.)?" The response, in virtually all but a few circumstances, is that you should use a Stored Query in place of the parallel SQL Statement. The brief explanation that follows will explain the reasoning behind this:

1. When you store a Query as a Database Object, Access analyzes the Query and stores an optimized version.
2. When you run a Stored Query, you are running a version of the Query that has already been optimized as explained in Item #1.
3. Should you change a Query, Access will again analyze it the next time you run it and it will again store the newly optimized version. This optimization will persist until if, and when, you make another change to the Query.
4. Each time you run an SQL Statement, Access analyzes the Statement and determines the optimal way to execute it.
5. This 'Optimal Execution Path' is not stored internally for the SQL Statement as it is for its Stored Query counterpart.
6. Because the analysis and optimization take time, the SQL Statement usually executes more slowly than the equivalent Stored Procedure.