Home Dashboard Directory Help
Search

Partition Wise Joins by geoff patterson


Status: 

Closed
 as Won't Fix Help for as Won't Fix


20
0
Sign in
to vote
Type: Suggestion
ID: 759266
Opened: 8/23/2012 10:53:04 AM
Access Restriction: Public
0
Workaround(s)
view

Description

When joining two tables that are both partitioned on the same column and partition function, SQL Server does not take advantage of the fact that each partition could be processed independently and the results concatenated or aggregated at the end.

In such cases, a query plan that allocates one thread to each partition and performs a MERGE JOIN within that partition--with that thread not touching data from other partitions at all--would likely be a highly efficient way of parallelizing the join such that performance scales linearly with the number of CPU cores used (so long as there are enough partitions involved).

The attached test file builds a data set and includes test queries and statistics that highlight the current inefficiency of SQL Server in this particular case. The test file can be run in any environment, but it is likely that the most meaningful results will be seen on machines with 8+ cores.

For reference, here is an Oracle blog entry describing functionality similar to this request in Oracle:
https://blogs.oracle.com/datawarehousing/entry/partition_wise_joins

In addition, here is a relevant StackExchange question on the topic:
http://dba.stackexchange.com/questions/22217/sql-server-does-not-optimize-parallel-merge-join-on-two-equivalently-partitioned
Details
Sign in to post a comment.
Posted by Paul White NZ on 6/15/2013 at 8:34 AM
Hi Gus,

It is curious that collocated hash join has been implemented (presumably because the scenario was common enough) but this suggestion to add collocated merge join has been rejected.

Paul
Posted by Microsoft on 4/30/2013 at 1:00 PM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenario reported in the bug are not common enough and due to the risk of implementing a fix it unfortunately does not meet the bar for the current version of the product.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by Microsoft on 8/29/2012 at 2:52 PM
Thanks for your feedback. We are looking into this request
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
PartitionWiseJoinMSFTConnectSuggestion.sql 8/23/2012 12 KB