![]() ![]() TH WHERE ProductID = 712 UNION ALL SELECT ProductID, TransactionID FROM dbo. TH WHERE ProductID = 921 UNION ALL SELECT ProductID, TransactionID FROM dbo. TH WHERE ProductID = 873 UNION ALL SELECT ProductID, TransactionID FROM dbo. TH WHERE ProductID = 870 UNION ALL SELECT ProductID, TransactionID FROM dbo. SELECT ProductID, TransactionID FROM dbo. Look at the execution plan chosen by the SQL Server query optimizer when we ask it to return the original T-SQL query results in Transaction ID order (by adding an ORDER BY clause): The point of the SSIS example can now be revealed. Indeed, if we try to add a Sort on Transaction ID after the final Merge, SSIS shows a warning to let us know the stream is already sorted in the desired fashion: The new Merge flow produces results in Transaction ID order, without requiring an expensive (and blocking) Sort component. Note that we need five separate Merge components because Merge is a binary component, unlike the SSIS "Union All" component, which was n-ary. A redesigned SSIS Data Flow that uses Merge to return the desired rows in Transaction ID order follows: In SSIS, the component that combines rows from two sorted data flows into a single sorted data flow is called "Merge". ![]() That would certainly do the job, but a skilled and experienced SSIS designer would realize there is a better option: read the source data for each product in Transaction ID order (utilizing the index), then use an order-preserving operation to combine the sets. One option would be to add an explicit Sort component after the Union All: Now imagine we need the final output from that data flow in Transaction ID order. We could certainly also write the whole thing as a single T-SQL statement in SSIS, but the more interesting option is to create a separate data source for each product, and use an SSIS "Union All" component in place of the SQL Server Concatenation operator: An SSIS "Execution Plan"įor reasons that will make sense in a moment, consider how we might design an SSIS package to perform the same task. Again, this is not guaranteed because we did not specify an ORDER BY clause, but it does show how Concatenation operates internally. When this plan is executed, there is a good chance that the result set will shows rows for product 870 first, then 873, 921, 712, 707, and finally product 711. I mentioned before that the Concatenation operator forms its output by reading from its inputs in sequence. None of that is guaranteed behaviour of course, it is just something interesting to observe. The topmost Index Seek is for product 870, the next one down is for product 873, then 921 and so on. The plan above features a separate Index Seek for each listed product ID, in the same order as specified in the query (reading top down). It produces an execution plan featuring a Concatenation operator with six inputs, as seen in SQL Sentry Plan Explorer: TH WHERE ProductID = 707 UNION ALL SELECT ProductID, TransactionID FROM dbo. To illustrate, let's revisit the AdventureWorks-based example from my previous article, "Rewriting Queries to Improve Performance": The Concatenation operator is an n-ary physical operator, meaning it can have '2…n' inputs. The Concatenation operator is relatively simple: its output is the result of fully reading from each of its inputs in sequence. This article describes the query tuning opportunities presented by Merge Join Concatenation, and details the internal behaviours and considerations you need to be aware of to make the most of it. ![]() The query optimizer does a reasonable job of choosing between the two options in many cases, but it is a long way from perfect in this area. While the logical operation is the same, there are important differences between the two physical operators that can make a tremendous difference to the efficiency of your execution plans. The SQL Server query execution engine has two ways to implement a logical 'union all' operation, using the Concatenation and Merge Join Concatenation physical operators. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |