How to remove Hash Match

Guru 2014-04-01 20:54:30

I have three tables and I have created indexes to all the tables but when I run join query I am getting Hash Match (Inner Join). Is there any way I can improve the performance?

/storage/attachments/821-Plan_Anonymized.pesession
Aaron Bertrand 2014-04-01 21:01:36
Don't know the table structure, but obviously the indexes you created do not better satisfy this query, since every table involved has a clustered index scan – I don't see any non-clustered index access at all, nor do I see any seeks (not that seeks are always better, depends on a lot of things). Without the table structure or a basic idea of what the query looks like, I don't think there is an easy "here is how to get rid of a hash join" advice, other than changing INNER JOIN to INNER LOOP JOIN or INNER MERGE JOIN. This will probably not "fix" performance on its own, and should only be used to test – leaving join hints in code can be a recipe for disaster.
Guru 2014-04-01 21:08:49
Table 1
ID Primary Key
table2ColID Foreign Key
table3ColID Foreign Key

Total# rows 1,612,728

NDX_Table2_Table2ColID
NDX_Table3_Table3COLID

Table 2
ID Primary Key
Desc

Total # rows 20

Table 3
ID Primary Key
Desc

Total # rows 7

SQLkiwi 2014-04-02 00:29:04
> I have three tables and I have created indexes to all the tables but when I run join query I am getting Hash Match (Inner Join). Is there any way I can improve the performance?

That execution plan pattern is typical for a star join query, see this White Paper for the details.

The idea of introducing bitmap filters is to reduce the number of fact table rows returned by the scan by rejecting rows that cannot possibly meet the dimension table criteria early.

This approach is not effective in your case, because all rows pass the bitmap filters; the fact table scan returns 1,612,728 rows, which is the same as the table cardinality.

The bitmap plan also targets systems with a reasonable number of logical processors available. Your query executes on only two processors, making more available to the query would improve performance.

Ultimately, there's not a terrific amount of obvious improvement for a query that returns all rows from the fact table. Had the dimension table filters been more selective, the bitmap filtering would be more effective (particularly if the foreign keys are integer types).

Had the query been very selective, with suitable nonclustered indexes on the fact table, the optimizer might also consider more sophisticated approaches.

April 1, 2014
No Comments