提高非聚集索引查找的性能

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

我有一个查询，其中索引搜索（非聚集）占用总执行时间的93％的更多时间。

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?

查询的执行计划，索引查找的估计行数是1，实际行数是209.这是否是问题？

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

如何提高非聚簇索引的性能。一般性答案将有所帮助。

Execution Plan: 提高非聚集索引查找的性能

执行计划：

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

1 个解决方案

#1

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.

这通常需要DBA类型权限。

Check out the Microsoft page on update statistics.

查看有关更新统计信息的Microsoft页面。

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

#1