Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Improving the performance of non clustered Index Seek

Tags:

sql

sql-server

I have a query where a index seek (non clustered) is taking more time about 93% of the total execution time.

the execution plan of the query, the estimated number of rows for the index seek is 1 and actual number of rows is 209. whether this is the problem?

How to improve the performance of a non clustered index seek. A generic answer will be helpful.

Execution Plan: enter image description here

And following is the query,

SELECT TOP 11 DVPR1.IncidentID, DVPR2.IncidentID, Rel.ID, PER1.[LastName], PER1.[FirstName]
FROM 
  DV_PHPersonalRecord DVPR1 
INNER JOIN Relationship Rel 
  ON  Rel.source_Id = DVPR1.RowId 
  AND Rel.typeCode = 'RPLC'
INNER JOIN DV_PHPersonalRecord DVPR2 
  ON  DVPR2.RowId = Rel.target_Id
INNER JOIN [T_Attribute] (nolock) 
  ON  [T_Attribute].[ActRelationship_ID] = Rel.[ID] 
  AND [T_Attribute].[name] = 'MergeFlag' 
  AND ( [T_Attribute].[valueString_Code] = 'pending')
INNER JOIN [Person] PER1 (nolock) 
  ON  DVPR1.[PersonDR]=PER1.[RowID]
INNER JOIN [Person] PER2 (nolock) 
  ON  DVPR2.[PersonDR]=PER2.[RowID]
WHERE 
    DVPR1.TypeDR = 718990 
  AND
    (PER1.[Code_ID] IS NULL OR ( PER1.[Code_ID] = '6516' 
                             AND PER1.[OptionsCode_ID] = '6522')
    )
  AND 
    ( PER2.[Code_ID] IS NULL OR ( PER2.[Code_ID] = '6516' 
                              AND PER2.[OptionsCode_ID] = '6522')
    )
ORDER BY PER1.[LastName] ASC,
        PER1.[FirstName] ASC
like image 864
arun.v1 Avatar asked Apr 19 '12 06:04

arun.v1


People also ask

Does nonclustered index improve performance?

It contains only a subset of the columns. It also contains a row locator looking back to the table's rows, or to the clustered index's key. Because of its smaller size (subset of columns), a non-clustered index can fit more rows in an index page, therefore resulting to an improved I/O performance.

How non-clustered index helps to fetch the data?

We specify the non-key column in the non-clustered index using the index clause. Included columns are part of the leaf node in an index tree. It helps to fetch the data from the index itself instead of traversing further for data retrieval.

Which is faster clustered or non-clustered index?

A clustered index may be the fastest for one SELECT statement but it may not necessarily be correct choice. SQL Server indices are b-trees. A non-clustered index just contains the indexed columns, with the leaf nodes of the b-tree being pointers to the approprate data page.


1 Answers

When I see such a discrepancy between expected rows and actual rows, I would first look at updating the statistics of all involved tables.

The query optimizer is supposed to do this automatically, but... sometimes it brings benefit.

This usually requires DBA type permissions.

Check out the Microsoft page on update statistics.

http://msdn.microsoft.com/en-us/library/ms187348.aspx

like image 81
GregHNZ Avatar answered Oct 14 '22 06:10

GregHNZ