![]() ![]() In the test we called stored procedure without passing The absence of the value instructed the code to use parameter’s optional value, = NULL. Query optimiser use the value passed into parameter to create query plans for all queries in the batch that references it. Previous experiment showed how Sql Server builds query plans for all code paths without knowing which one will be executed. The estimated number of rows is lower than the actual number of rows.įigure 2 shows negative effect of the cached, sub-optimal procedure plan, on the subsequent procedure calls. Rn = ROW_NUMBER() OVER(ORDER BY (c.)) -1 - generate 100K records starting from 0įROM sys.all_columns c, sys.all_columns c1 *SQL Server 2019 CTP3, Compatibility level 150 */ The same applies for ProductId 6000, or 3 different ProductIds. ![]() The number of orders makes 20% of all orders. ProductId=40000 participates in 20,000 orders. The same applies for ProductId 100,200,300 …4900, or 50 different ProductIds. The number of orders makes 0.1% of all orders. ProductId = 0 participates in 100 Orders. The script below creates a sample table with the following ProductId data distribution. If the product is not included in any of the sales Orders, the code returns nothing or a warning message. Take for example a simple, non-production process that selects all orders and their details associated with a productId. Our TSQL code may implement logic which use conditional branching to decide what business rule to apply. Conditional branching in stored procedures In this post I’ll try to explain how Query optimiser handle queries in different conditional branches and how it relates to the option(recompile) hint and the procedure plan. There are many cases when programmers use conditional branching in tsql code to execute different queries or similar queries with different predicates based on a certain condition. plan is generated for all branch pathsĬonditional branching and OPTION(RECOMPILE) Conditional branching in stored procedures.Conditional branching, OPTION(Recompile) and procedure plan.We should use RECOMPILE option only when the cost of generating a new execution plan is much less then the performance improvement which we got by using RECOMPILE option. This is because of the WITH RECOMPILE option, here each execution of stored procedure generates a new execution plan. Here you see the better execution plan and great improvement in Statistics IO. Now execute this stored procedure as: set statistics IO on Now again creating that stored procedure with RECOMPILE option. ![]() Here when we execute stored procedure again it uses the same execution plan with clustered index which is stored in procedure cache, while we know that if it uses non clustered index to retrieve the data here then performance will be fast. Now executing the same procedure with different parameter value: set statistics IO on The output of this execution generates below mention statistics and Execution plan: Select address,name from xtdetails where execute this stored procedure as: set statistics IO on ![]() Now create stored procedure as shown below: create procedure as varchar(50)) Set into xtdetails table xtdetails contains 10000 rows, where only 10 rows having name = asheesh and address=Moradabad. Now, I am inserting the data into this table: declare as int Ĭreate clustered index IX_xtdetails_id on xtdetails(id)Ĭreate Nonclustered index IX_xtdetails_address on xtdetails(address) In this case if we reuse the same plan for different values of parameters then performance may degrade.įor Example, create a table xtdetails and create indexes on them and insert some data as shown below: CREATE TABLE. But sometimes plans generation depends on parameter values of stored procedures. If plan found in cache then it reuse that plan that means we save our CPU cycles to generate a new plan. If we again execute the same procedure then before creating a new execution plan sql server search that plan in procedure cache. When we execute stored procedure then sql server create an execution plan for that procedure and stored that plan in procedure cache. Here i am focusing on why we use WITH RECOMPILE option. Some time, we also use WITH RECOMPILE option in stored procedures. We use stored procedures in sql server to get the benefit of reusability. Today here, I am explaining the Use of Recompile Clause in SQL Server Stored Procedures. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |