{HASH | ORDER} GROUP in Visual Basic .NET

Drawer UPC A in Visual Basic .NET {HASH | ORDER} GROUP

SQL Server has two possible implementations for GROUP BY (and DISTINCT). It can be implemented by sorting the rows and then using the fact that rows in the same group are now adjacent physically. It can also hash each group into a different memory location. When one of these options is speci ed, it is implemented by turning off the implementation rule for the other physical operator. Note that this applies to all GROUP BY operations within a query, including those from views included in the query.

8

Many data warehouse queries have a common pattern of a number of joins followed by an aggregate operation. If the estimates for the number of rows returned in the joins section is in error, the estimated size for the aggregate operation can be substantially incorrect. If it is underestimated, then a sort and a stream aggregate may be chosen. As memory is allocated to each operator based on the estimated cardinality estimates, an underestimation could cause the sort to spill to disk. In a case like this, hinting a hash algorithm might be a good option. Similarly, if memory is scarce or there are more distinct grouping values than expected, then perhaps using a stream aggregate would be more appropriate. This hint is a good way to affect system performance, especially in larger queries and in situations when many queries are being run at once on a system.

Many people incorrectly use UNION in queries when they likely want to use UNION ALL, perhaps because it is shorter. UNION ALL is actually a faster operation, in general, because it takes rows from each input and simply returns them all. UNION actually has to compare rows from each side and make sure that no duplicates are returned. Essentially, UNION performs a UNION ALL and then a GROUP BY operation over all output columns. In some cases, the Query Optimizer can determine that the output columns contain a key that is unique over both inputs and can convert the UNION to a UNION ALL, but in general, it is worth making sure that you are actually asking the right query. These three hints apply only to UNION. Now, assuming that you have the right operation, you can pick among three join patterns, and these hints let you specify which one to use. This example shows the MERGE UNION hint.

CREATE CREATE go INSERT INSERT TABLE t1 (col1 INT); TABLE t2 (col1 INT); INTO t1(col1) VALUES (1), (2); INTO t2(col1) VALUES (1);

SELECT * FROM t1 UNION SELECT * FROM t2 OPTION (MERGE UNION);

As you can see, each hint forces a different query plan pattern. MERGE UNION is useful when there are common input sizes. CONCAT UNION is best at low-cardinality plans (one sort). HASH UNION works best when there is a small input that can be used to make a hash table against which the other inputs can be compared. UNION hinting is done for roughly the same reasons as GROUP BY hinting both operations are commonly used near the top of a query de nition, and they have the potential to suffer if there is error in cardinality estimation in a query with many joins. Typically, one either hints to the HASH operator to address cardinality underestimation or hints to the CONCAT operator to address overestimation.

Join order and algorithm hints are common techniques to x poor plan choices. When estimating the number of rows that qualify a join, the best algorithm depends on factors such as the cardinality of the inputs, the histograms over those inputs (which are used to make estimates about how many rows qualify the join condition), the available memory to store data in memory such as hash tables, and what indexes are available (which can speed up loops join scenarios). If the cardinality or histograms are not representative of the input, then a poor join order or algorithm can result. In addition, there can be correlations in data across joins that are extremely dif cult to model with current technologies (even ltered statistics in SQL Server 2008 work only within a single table).