It is known from the stored procedure above that there is only one SQL code in the stored procedure, and that the recompilation of the stored procedure level and the compilation of the SQL statement level are all compiled by the SQL If you say that with recompile stored procedure-level recompilation and option recompile, the SQL statement-level recompilation effect is the same, Similarities and differences in the execution plan generated by recompile with RECOMPILE and OPTION (RECOMPILE) Here, first of all, thank uest Students for the reference and guidance suggestions.ĭifferences in use with RECOMPILE and OPTION (RECOMPILE)įor recompilation of stored procedure levels, the typical usage is as follows, specifying "with RECOMPILE" after stored procedure parametersĬREATE PROCEDURE testrecompile_withrecompile ( int) with Recompileasbegin SET NOCOUNT on SELECT * from testrecompile WHERE Id = OR is Nullendgoįor statement-level recompilation, the typical usage is to specify option at the end of a SQL statement (RECOMPILE)ĬREATE PROCEDURE testrecompile_optionrecompile ( VARCHAR) asbegin SET NOCOUNT on SELECT * from testrecompile WHERE Id = OR is NULL OPTION (RECOMPILE) EndgoĪs a rule, build a test environment firstĬreate a testrecompile table, which is the table used in the stored procedure above, insert 100W rows of data, create an index named IDX_ID on the ID fieldĬREATE TABLE testrecompile ( Id int, Value varchar ()) godeclare int = 0WHILE INSERT Into Testrecompile VALUES ()) SET = INDEX idx_id on Testrecompile (Id) GO This article uses a simple example to illustrate the difference between the two (the test environment is SQL SERVER2014). Here comes another question: the Parameter embedding optimization (how to translate?) There is no Chinese reference to the Parameter embedding optimization, reluctantly translated as "parametric implant optimization") In fact, in some specific cases, the difference between the two is not just the difference between stored procedure-level recompilation and statement-level recompilation,įrom the execution plan generated by the compilation, the intrinsic mechanism differences between the two methods of forced compilation are still relatively large. It is relatively shallow to understand that the former is to compile all the statements in the entire stored procedure, the latter is to recompile a stored procedure in one of the statements, and did not investigate whether there is only such a little difference.
One is based on the stored procedure level recompilation with RECOMPILE, and the other is option (RECOMPILE)-based statement-level recompilation. There are two ways to implement forced recompilation when considering recompiling T-SQL (or stored procedures), if you ignore other factors that cause recompilation, such as rebuilding indexes, updating statistics, and so on.
0 kommentar(er)
