View Complete Post
Jared Parsons demonstrates a technique to achieve maximum performance from LINQ queries on large sets of data in order to create a responsive user interface.
MSDN Magazine August 2008
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.
Do I need to create Index on views as well to achieve the performance.
Thanks in advance
I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column
which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.
I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.
Could you please suggest what i can do to improve the performance.
I have two subqueries which build a string (varchar(200)), first subquery creates 200000 records and second 50000 records, how can I write a select statement to get records from subquery first with string existing in second subquery records.
Will sorting both the sub query results be helpful? Kindly advice for best performing select statement.
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 ******/
I have this QUERY:
CASE 1 :
SET AccCount = Accounts
FROM @temRiskDetails t
SELECT Audit_Id, NODE_Id, COUNT(*) AS Accounts FROM As_Daily_Monthly_Node_Form_Input
WHERE Audit_ID = @AuditId AND Delete_flag = 0 AND Label_Value = 'OPEN'
GROUP BY Audit_Id, NODE_Id
ON A.NODE_Id = NodeId
CASE 2 :
Placing the Inner query(inline view )(the bold letter portion) data into a temp table and joing that temp table with the @temRiskDetails (as specified in the CASE 1 related query).