Any SQL statement goes through 4 phases..
1) Parsing the syntax of sql statement.
2) Translating to query tree.
3) Develop execution plan.
4) Execute.
Now,
In case "Stored Procedures".
1st & 2nd phase comes once, at creation.
3rd phase comes, if it is not in memory cache.
4th is the only step that runs each time.
Thats why stored procedures are fast, as in this first 2 steps runs only once at the time of creation, and we always says stored procedures are precompiled, then question is what is precompiled, the answer is the execution plan is precompiled i.e. the 3rd step.
Stored procedure contains multiple sql statements, even then it is treated as a single statement and the only step runs that is the execution everytime.
Note:- "Execution Plan" is created when it is not in memory, execution plan is not created on stored procedure compilation but on the first use of stored procedure, and reuse that execution plan on subsequent uses.
That is what i mean to say 3rd step runs when it is not in memory, if it is in memory then skips that step and runs the final step that is "Execution".
Hope will be helpful in understanding "Store Procedure" :)
No comments:
Post a Comment