![]() Selectivity = (rows that pass the predicate / total rows in the table). High selectivity = high uniqueness = low number of matching values. * Selectivity represents uniqueness of values in a column. Lets say that our procedure call pattern assumes similar probability of passing “small”, more selective*(returns only 100 rows) and “big”, less selective(returns 20,000 rows) ProductId values. There is 15% ProductIds (10 out of 66) that returns 500rows and only 3% ProductIds (3 out of 66) that returns 10,000 and 20,000 rows. However, most of the ProductIds, 76% (50 out of 66 different ProductIds) returns the same number of rows(100 rows). As a consequence, QO used an atypical value to build and cache a sub-optimal query plan, which then had a negative impact on all subsequent procedure calls.īut the effect could be the same even if we passed any value to shows that the values in ProductId column are not evenly distributed (which is usually true for the production systems □ ). 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |