Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mcolli00
Starting Member
19 Posts |
 Posted - 2011-03-21 : 11:24:16
|
| HiI am having trouble with a stored proc that contains the following sql statement. I am getting a 66% hash match from the group by clause and a 34% index scan on squarefeet column's index. Do you know how to reduce the hash match to loop or something with better performance by changing this query logic? Note: The squareFeet column is int data type and has numerous null values in it.Thanks in advance! MCselect sum(SquareFeet) from area group by area_fk |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-21 : 13:51:54
|
| Query, table definitions and index definitions please. If you can find somewhere to upload the execution plan (saved as .sqlplan file and zipped) that would be a bonus.btw, a loop join does not necessarily have better performance than a hash join. On larger row counts it generally has a far, far, far worse performance.http://sqlinthewild.co.za/index.php/2009/11/24/the-most-optimal-join-type/p.s. That query you've posted cannot have a hash match in the exec plan (unless that's a view). It can have a hash aggregate, but not a hash match (join)--Gail ShawSQL Server MVP |
 |
|
|
mcolli00
Starting Member
19 Posts |
Posted - 2011-03-21 : 17:44:53
|
| Yes you are correct. It was a hash aggregate. I couldn't update the objects for privacy reasons. I have resolved the issue -- a covering index did the trick.Thanks to all of those who replied. MC |
|
|
|
|
|
|
aggregate query with hash match