Refreshing an execution plan for a query
View Patent ↗An improved system and method for storing, reusing and recompiling execution plans for SQL database queries. The system. The system validates the performance of a newly compiled plan prior to replacing a currently stored execution plan and compares the resource cost of the recompiled plan with the cost of the currently stored plan. In the event that the resource cost of the recompiled plan is not greater than the stored plan resource cost, the system replaces the currently stored execution plan with the recompiled plan.
1. A method of refreshing a stored execution plan for a database query where the stored execution plan comprises a compiled form of the query, the stored execution plan having a stored plan resource cost associated therewith, said method comprising:
compiling the database query;
executing the compiled query;
determining the actual resource cost of the execution of the compiled query;
comparing the determined resource cost of the compiled query with the stored plan resource cost; and
in the event that the determined cost is not worse than the stored plan resource cost, replacing the stored execution plan with the compiled query.
2. A method as claimed in claim 1 , wherein the actual resource cost is stored in association with the compiled query.
3. A method as claimed in claim 1 , wherein in the event that the determined cost is worse than the stored plan resource cost then the compiled query is discarded.
4. A computer program stored on a computer readable medium for refreshing an execution plan for a database query, where the stored execution plan comprises a compiled form of the query, said computer program comprising instructions to cause a computer processor to:
compile the query;
execute the compiled query;
determine the actual resource cost of execution of the compiled query;
compare the determine resource cost of the compiled query with a stored plan resource cost; and
in the event that the determined resource cost is not worse than the stored plan resource cost, replace the stored execution plan with the compiled query.