查询视图添加额外WHERE条件后性能骤降，求优化建议

问题背景 #

执行类似以下查询时，仅保留日期条件速度正常，但添加Visit_code not in ('12','13')或mode_code <>'99'任意一个条件后，查询耗时数小时；按执行计划建议新增非聚集索引后，性能反而下降。无法提供完整实际语句和执行计划，需排查思路。

查询条件：

where Date_Time between '2021-11-01 00:00:00.000' and '2022-11-02 00:00:00.000'
  and Visit_code not in ('12', '13')
  and mode_code <>'99'

视图定义：

CREATE VIEW [dbo].[vw_Test] AS
 select fields
  
  from table1 ed
  left join table2 e on ed.field1_id = e.field1_id
  left join table3 et on et.field1_id = ed.field1_id    
  left join table4 etf on etf.field1_id = e.field1_id
                            and etf.field2_cd= 85429041                                 
                            and etf.dt_tm_field >= '2025-01-01 00:00:00.0000000'
  left join table5 etf_dt on  etf_dt.field1 = e.field1
                            and etf_dt.field3= 85429039                                 
                            and etf_dt.dt_tm_field >=  '2025-01-01 00:00:00.0000000'
  left join table6 ei on ei.field1 = ed.field1
                                        and ei.field4_cd = 123485.00
    left join table7 cvo_ModeOfArrival on cvo_ModeOfArrival.field = ed.field6
                                                 and cvo_ModeOfArrival.field5 = 12345
    left join table7 cvo_ModeOfSep on cvo_ModeOfSep.field = ei.field7
                                                 and cvo_ModeOfSep.field5 = 23456
    left join table7 cvo_FinancialClass on cvo_FinancialClass.field = e.field8
                                                 and cvo_FinancialClass.field5 = 34567
    left join table7 cvo_Specialty on cvo_Specialty.field = e.field9
                                                 and cvo_Specialty.field5 = 45678
  left join table8 ea on ea.field1_id = e.field1_id
    left join table7 cvo_ea on cvo_ea.field = ea.field10
                                                 and cvo_ea.field11 = 345666
GO

排查方向 #

1. 索引有效性验证 #

• 检查新增索引的实际使用情况：执行SELECT * FROM sys.dm_db_index_usage_stats WHERE object_id = OBJECT_ID('目标表名')，确认索引是否被查询调用，若未被使用则直接删除，避免额外维护开销。
• 检查索引覆盖度：确认索引是否包含查询所需的所有字段（包括连接字段、过滤字段、返回字段），未覆盖的索引会触发键查找/书签查找，反而增加IO开销。
• 尝试创建过滤索引：针对Visit_code not in ('12','13')或mode_code <>'99'的过滤逻辑，创建过滤索引（如CREATE NONCLUSTERED INDEX IX_Table_VisitCode ON TableX(Visit_code) WHERE Visit_code NOT IN ('12','13')），缩小索引扫描范围。

2. 执行计划与统计信息检查 #

• 更新统计信息：执行UPDATE STATISTICS [涉及的所有表名] WITH FULLSCAN，过时的统计信息会导致优化器预估行数错误，选择低效执行计划。
• 排查连接方式异常：若查询出现嵌套循环+大量键查找，说明优化器错误预估了返回行数，可尝试强制使用哈希匹配或合并连接（仅临时测试，不建议长期硬编码）。
• 检查tempdb状态：查看tempdb的空间使用和IO延迟，若哈希匹配或排序操作溢出到磁盘，会导致性能骤降，需扩容tempdb或优化查询减少内存开销。

3. 查询逻辑优化 #

• 替换NOT IN写法：用NOT EXISTS或LEFT JOIN + IS NULL替代Visit_code not in ('12','13')，例如：

  AND NOT EXISTS (SELECT 1 FROM 对应表 WHERE 关联条件 AND Visit_code IN ('12','13'))
  

  避免NOT IN可能引发的隐式转换或空值处理异常。
• 推过滤条件到视图内部：如果Visit_code或mode_code来自左连接的表，尝试将过滤条件加到视图的JOIN子句中（需确认业务逻辑允许），减少中间结果集大小。
• 检查字段选择性：统计mode_code = '99'的行数占比，如果该值占比极低，优化器可能选择全表扫描；若占比极高但统计信息未更新，会导致执行计划错误。

4. 系统资源排查 #

• 监控CPU、内存使用率：查询执行时是否出现CPU耗尽（100%）或内存不足，导致查询等待资源。
• 检查磁盘IO：用性能监视器查看磁盘读写延迟，若延迟过高，说明磁盘成为瓶颈，可考虑迁移数据到更快的存储介质。

内容的提问来源于stack exchange，提问作者Doodle