SQL 提高非聚集索引查找的性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10222781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Improving the performance of non clustered Index Seek
提问by arun.v1
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
回答by GregHNZ
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 页面。
