Query performance – hash match
I am very new to query optimization.
Is there a way to avoid the hash matches. I think I cant filter out any data as all data is required by business.
Also the Actual row and Estimated for object 3 have a huge difference.
Thanks in advance
Simple filtering conditions are relatively easy to produce estimates for, but more complex predicates, joins, and aggregations will always result in a best-effort estimate.
The important question is whether the difference is likely to cause a poor plan to be chosen. In your case, the nature of the query is such that hash joins are likely to be a good strategy. If the raw number of rows were much larger than 743, it might be more of a concern, because the estimate of 148 rows is used to size the memory allocation for the hash table in that join. As it is, the minimal size of the hash table will easily accomodate 743 rows.
So, it's not often the ratio of the difference between estimate and actual that is important; most often it's about the raw numbers, and how that might affect plan selection.