469,648 Members | 1,158 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Stored Query vs SQL Statement

ADezii
8,800 Expert 8TB
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.
Jun 3 '07 #1
0 9433

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Dino L. | last post: by
7 posts views Thread by JIM.H. | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.