Menu
This is the description from Microsoft TechNet explaining Trace Flag 342 (emphasis added):
Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins. One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge joins because the query is forced to use the order specified in the FROM clause.
Does any of you know what is a pseudo-merge join? As far as I know, SQL Server has 3 Join Algorithms (Nest Loop Join, Merge Join, and Hash Join - which encompass Bitmap Join). So what is a pseudo-merge join, and what is the difference between it and a regular Merge Join or any other join for that matter?
894
Merge Join. Introduction. The Merge Join operator is one of four operators that join data from two input streams into a single combined output stream. As such, it has two inputs, called the left and right input. In a graphical execution plan, the left input is displayed on the top.
For Concatenation and Union, the Merge Join algorithm is always based on comparing all columns from both inputs. The Where (join columns) property is not listed in this case.
The Merge Join operator is in most cases fully order-preserving for both the left and the right input. For a many to many Merge Join, the order of the left input is fully preserved; the order of the right input is only partially conserved.
Within a pseudocode merge sort algorithm, we need to use selection (IF statements), iteration (WHILE loops), and arrays! Merge sort algorithms are often very efficient due to only searching half of a given data set. Within pseudocode, merge sorts can be written within few lines of code.
I know this is a kind of old question but I will try to answer it as specific as I can.
Pseudo-merge is not a type of Join used as a T-SQL language operator, my interpretation of Microsoft's explanation that using the Trace Flag 342 is as folows:
Disables the costing of pseudo-merge joins, thus significantly reducing time spent on the parse for certain types of large, multi-table joins.
Pseudo-merge is the concept to represent that the query optimiser is trying to calculate a better query execution plan, trying to obtain the best way to join the several tables.
One can also use SET FORCEPLAN ON to disable the costing of pseudo-merge joins because the query is forced to use the order specified in the FROM clause.
This option prevents the optimizer from trying to calculate and simply execute the joins as they are listed in the query.
An article on SET FORCEPLAN ON for reference.
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
