CODEWITHSUNDEEP
sqlsql-server-2008t-sqldatabase-performance
Avi
Avi

Reputation: 1193

Speed up Hash Match operator on one table

I have one temp table with 1 million rows, and i just need to go through all the rows and do some basic scalar computation, something like this:

SELECT m.BatteryID, m.CarID, /*calculating some scalar*/
FROM #Return m 
GROUP BY m.CarID, m.YearTime, m.BatteryID;

The table has one identity 1,1 Clustered indexed PK.

The query plan looks like this: enter image description here

It shows that i have a clustered index scan which is fine, since i always use all rows from the table, hence the tree is traveled from top to bottom. Also, not the clustered index scan is the most time consuming (17%), but the Hash Match (65%) Is there any way to improve the performance on such simple scenario?

Thanks!

Upvotes: 3

Views: 1753

Answers (1)

Avi
Avi

Reputation: 1193

After adding index as (CarId, YearTime, BatteryId), it becomes 8% StreamAggregate and 90% Clustered Index scan. Thanks Gordon

Upvotes: 2

Related Questions