SQL Server Performance Forum – Threads Archive
Execution PlanWe have a real estate system in development. We need the listing page to be lightening fast. Currently it is around the 1.5 second mark. Alot of the execution plan is Hash Match. Can I make this faster?
suppose your query is: SELECT Description
WHERE Location = x
GROUP BY Price and you have an index with the column order: Location, Description, Price
your execution plan should be (starting from the right side of Display Estimated Execution Plan) index seek of Listings for all Location = x values , a Hash match to group the Price, and then some other stuff
(actually this query doesn’t work, but i am trying to phrase it real estate terms, and get the Hash Match that you have in your plan)
If you were to change the index order to: Location, Price, Description,
the Hash Match is replaced by a stream aggregate, which should be more efficient if you post your query and the relevent indexes, i can probably give you a better explanation
Great explaination Joe. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
now its the bookmark lookup that’s the major cost for the query. any ways to get this faster?
you should post the query and table definition & index scripts in order for us to give a good answer without an overly long list of exceptions and considerations