download<\/a>.<\/p>\nSolution Space: T-SQL Queries<\/h2>\n
The first goal is to create a result set against which the software result can be verified. Adhering to the business rules and requirements, the query constrains on four logical (Boolean-valued) operators, LIKE, IN, AND, and OR:<\/p>\n
![\"Text\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/text-description-automatically-generated-with-med.png\")
<\/p>\n
Figure 4. Original query that creates rowset for software verification<\/strong><\/p>\nThis is the query outcome over the sample rows:<\/p>\n
![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/graphical-user-interface-description-automaticall.png\")
<\/p>\n
Figure 5. Verification rowset with single-step query plan.<\/strong><\/p>\nThe Index Seek operator over the covering index expands the IN() operator, which implies logical OR, into Seek Keys (for a range scan count of 3), and further constrains on the Predicate as expected:<\/p>\n
![\"Text\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/text-description-automatically-generated.png\")
<\/p>\n
Figure 6. Bottom part of expansion for Index Seek operator<\/strong><\/p>\nAll is correct to this point, and I can convert the index into a structure that will be used twice in software. However, the trivial query plan is not as useful as it could be. Relational theory is based on set theory, and the query rewrites next will more directly reflect this. This is important because the transformation algorithms in the software will all be set-based.<\/p>\n
Query Rewrite I<\/h2>\n
The second goal, as you recall, is to fashion a query whose query plan will serve as a model for the software design. The trick I\u2019ll employ is to replace the logical connectives OR and AND with their T-SQL equivalent set operators UNION and INTERSECT.<\/p>\n
![\"Text\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/text-description-automatically-generated-1.png\")
<\/p>\n
Figure 7. Query rewrite using set-based operators<\/strong><\/p>\nIt produces the detailed query plan I\u2019m looking for:<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/a-screenshot-of-a-computer-description-automatica.png\")
<\/p>\n
Figure 8. Query plan for Figure 7<\/strong><\/p>\nThe outer input uses the same non-clustered covering index and Seek Keys for range scans (scan count remains 3) as the original query but without the Predicate. Instead, each outer row directly joins via Nested Loops (Inner Join)<\/em> on all columns to the rowset produced by the union of the Creamery and base flavor rows (discussed next). The Nested Loops operator, then, accomplishes a row-by-row set intersection.<\/p>\nIn the inner input to Nested Loops, the upper Clustered Index Seek<\/em> on the primary key is for the LIKE(% Creamery) condition. Although the value to the LIKE logical operator is a non-SARG, a true index seek range scan (not table scan) is done on each outer row\u2019s (Maker, Flavor) prefix, during which the LIKE Predicate can be applied. The lower Clustered Index Seek parallels the upper, differing only in the predicate condition: BaseFlavor = N\u2018Vanilla.\u2019 Since the Inner Join is done on the superkey \u2013 i.e. all columns \u2013 and since flavor \u2192 base flavor, either lower input branch can produce at most one row. If both produce a row, they must, therefore, be the same row.<\/p>\nThe Concatenation<\/em> simply appends one output to the other \u2013 think UNION ALL in T-SQL. To get the T-SQL UNION, then, the Stream Aggregate (Aggregate)<\/em> operator collapses duplicates via grouping (and no aggregate expressions are computed). Specifically, both Concatenation and Stream Aggregate show only the same Output List, which consists of column header Union 1004, Union 1005, and Union 1006, and their rows are returned to the query.<\/p>\nThis query plan lends all the information needed to create index simulators and filter and map operations and algorithms in the software. An optimization, though, remains unaddressed \u2013 so almost all.<\/p>\n
Query Rewrite II<\/h2>\n
There is no index on the BaseFlavor column, but in the middle subquery from Figure 7 above, BaseFlavor is constrained on value \u2018Vanilla.\u2019 This suggests that the lower-level software might need a data structure(s) and algorithm(s) to support the restriction. To start the analysis, I\u2019ll create this covering index, which parallels that on the Flavor column:<\/p>\n
![\"Text\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/text-description-automatically-generated-2.png\")
<\/p>\n
Figure 9. Covering index on BaseFlavor key <\/strong><\/p>\nThe proposed index has no effect on the query plan in Figure 8 above, nor the original query plan in Figure 5. This is not proof, though, that separate resources are not needed in software to enforce the restriction.<\/p>\n
The next illustration shows the subquery run in isolation. A minor point is that the Query Optimizer chooses an Index Seek on the new index in place of an Index Scan using IX_MakerFlavor_Flavor. The central point is that subqueries that form part of a larger, single-statement T-SQL query will be translated into individual software resources that can and will be accessed in isolation.<\/p>\n
![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/a-screenshot-of-a-computer-description-automatica-1.png\")
<\/p>\n
Figure 10. Query in lower half of UNION run in isolation<\/strong><\/p>\nNow rerun the query but this time constrain the \u2018Vanilla\u2019 value by the Flavor column:<\/p>\n
![\"Table\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/table-description-automatically-generated.png\")
<\/p>\n
Figure 11. Constraining on Flavor column yields two fewer rows<\/strong><\/p>\nThis is not an idle thought experiment. The former result set contains two extraneous rows \u2013 column Flavor values being \u2018French Vanilla\u2019 and \u2018Cherry Vanilla\u2019 \u2013 that are not in the result set from Figure 4. The software effort needs a decision as to which constraint to enforce. Whether in queries or software transformations, performance is better served when rows not needed are culled early. The question I posed early on regarding the mismatch between the business rules and requirement can now be restated: Can the restriction on BaseFlavor be replaced by that on Flavor and still be correct?<\/p>\n
Constraining on the one side of a many-to-one relationship as does the top query form (BaseFlavor) is not wrong per se but is the root of the performance issue here. A business rule, however, signals the solution. The second states flavor \u2192 base flavor. Given this, and since the query\u2019s set intersection operation removes rows not matching on the Flavor column, the T-SQL can constrain on the many side (Flavor) without affecting correctness. The implication for software is that it obviates the need for special structures \u2013 those for simulating the proposed index to start the section \u2013 and handling (algorithms), as will be made clear in Part II.<\/p>\n
![\"Text\n\nDescription](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/text-description-automatically-generated-3.png\")
<\/p>\n
Figure 12. Query in final form<\/strong><\/p>\nTesting: A Note on the Sample Data<\/h2>\n
Say set A denotes the rows produced by the query that restricts on the three flavor names and set B the rows from the union of the two other queries. Given the sample data, it happens that A B and hence B \u2229 A = A. Therefore, the query on flavor names alone yields the same result as the full query and the knowledge of which can aid testing.<\/p>\n
It is important to realize that this outcome is entirely data-dependent. The proper subset relationship may not hold over an operating system file having different data, and the software will make no assumptions regarding this limited analysis. In general, it is never a good idea to deduce business rules from data that can change over time. With that caveat in mind, sample runs done here and in software over these<\/em> rows<\/em> can only boost confidence in results.<\/p>\n
![\"Diagram,](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/diagram-venn-diagram-description-automatically-g.png\")
<\/p>\n![\"A](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/a-picture-containing-text-black-scoreboard-scre.png\")
<\/p>\n![\"Graphical](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2021\/02\/graphical-user-interface-text-application-descr.png\")
<\/p>\n