If we reverse the written order of the tables in the query like so:Ĭ. We will assume that is deliberate and final ordering is inconsequential to the task at hand. Observe also that the order of the final result rows is not specified either, since we did not use a top-level ORDER BY clause. Of interest (though this is not guaranteed) notice that the order of the inputs follows the textual order of the original query. Both inputs will be fully read to return all result rows. The order of the inputs to the Concatenation does not matter here, because we have not set a row goal. It is drawing our attention to the I/O cost of the residual predicate hidden within the scan. The warning on the Table Scan operator is added by Sentry One Plan Explorer. The warning on the root SELECT operator is alerting us to the obvious missing index on the heap table. The execution plan produced by the query optimizer is: One table is a heap with no nonclustered indexes the other has a unique clustered index:Į. The following examples use two tables with identical contents: A million rows of integers from one to a million. This arrangement has the advantage of not increasing the cost-based plan search space (potentially one alternative for each possible reordering), while still producing a plan that is optimized to return the first row quickly. This is not done during cost-based optimization (as one might expect), but rather as a last-minute post-optimization rewrite of the normal optimizer output. In this specific circumstance, SQL Server will reorder concatenation inputs on the basis of expected cost. The limited support in versions up to SQL Server 2008 R2 applies where there is a goal of exactly one row. the client requires all rows), it does not generally matter in which order the concatenation inputs are read: Each input will be fully processed eventually in any case. Row goals can be set in a number of ways, for example using TOP, a FAST n query hint, or by using EXISTS (which by its nature needs to find at most one row). When a row goal is in effect, the optimizer tries to find an execution plan that will produce the first few rows quickly. By default, SQL Server optimizes execution plans on the basis that all qualifying rows will be returned to the client. Intuitively, the order of concatenation inputs only matters if there is a row goal. More precisely, there was some limited support for concatenation input reordering in SQL Server releases up to 2008 R2, but this was removed in SQL Server 2012, and has not resurfaced since. In fact, the SQL Server optimizer does not do this. For example, the optimizer could consider the benefits of rewriting A UNION ALL B as B UNION ALL A. Given that the SQL Server optimizer can often reorder things like joins and aggregates to improve performance, it is quite reasonable to expect that SQL Server would also consider reordering concatenation inputs, where this would provide an advantage. The concatenation of two or more data sets is most commonly expressed in T-SQL using the UNION ALL clause.
0 kommentar(er)
