This method is forced the server for recompiling the stored procedure. If we need each and every time then go for the first method. If next time we call the procedure without recompiling, the stored procedure will execute from the execution plan. In my current environment, I have many instances of stored procedures, just like the one shown below, where a bunch of parameters are passed to the procedure, and then within the procedure a select exists is run and based on the result, different logic paths are run within the stored procedure. This kind of execution is for one time only. We can execute the stored procedure with this recompiling option. The stored procedure which has parameter should be recompile for the parameter based results.Ĭreate Procedure Precuderenam e With RecompileĬreate Procedure MyProcedure WITH RECOMPILE The stored procedure result will vary based on the parameter passed. Use WITH RECOMPILE option if the stored procedure has parameters. Sql Server compile this each and every time of execution. If we create stored procedure with recompiling option sql server doesn't get the execution plan for this stored procedure. Look at the query plans - one should be optimized for one set of parameters, the other for the other set. Add where clauses to each such that between them they cover all possible cases. We can set the recompiling option while creating the stored procedure. The technique is this - break the stored procedure into 2, one meant for one set of parameters, one for another. This is for set the recompiling option for the existing stored procedures.Ĭreating Stored Procedure with Recompiling option The existing stored procedure can be set for the recompiling from the upcoming executions. Recompiling for an Existing Stored Procedure If the programmer thinks to compile the store procedure each and every time then, can use this method to recompile the stored procedure. Normal execution doesn't tries to create a new data access plan from the database, It tries to get the information from the execution plan. By the normal way of stored procedure execution, the stored procedure will calls from the execution plan. place so SQL Server will have to recompile the stored procedure during execution. Recompiling a stored procedure is necessary when the stored procedure changes every time of calling.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |