I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.
In my execution plan 46% goes for Hash Match Aggregate &
10% for HashMatch (JOINS)
32% Index seeks.
Is there a way I can optimize Hash Match Aggregates?
My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins,
SQL is executing Hash Match Joins.
Here is my stored proc code. Exec plan is in Excel Spreadsheet form.
/****** Object: StoredProcedure [dbo].[sp_GetBlockSpecimenInfo] Script Date: 09/23/2010 17:26:11 ******/
View Complete Post