根据Where子句，SQL Server查询超时问题

Question

我有一个查询，使用了3个函数和一些不同的视图，在此不方便公开。奇怪的是，当运行顶层查询时，拥有多个搜索关键字导致该查询需要花费约1小时才能运行，而将查询分成两部分后，每个查询只需要大约5秒钟。

以下是顶层查询：

Select * 
from  dbo.vwSimpleInvoice i 
inner join dbo.vwRPTInvoiceLineItemDetail d on i.InvoiceID = d.InvoiceID 

当我添加这个where子句时：

Where i.InvoiceID = 109581

查询需要大约3秒钟才能运行。同样地，当我添加这个where子句时：

Where i.InvoiceID = 109582

需要大约3秒钟时间。

但是当我添加了这个where子句：

Where i.InvoiceID in (109581, 109582)

我必须在大约50分钟后停止查询，而且它从未返回任何结果。

这是在运行SQL Server 2008 R2 Express的远程客户端服务器上发生的。当我本地运行它（也在SQL Server 2008 R2 Express上），我没有遇到巨大的延迟，最后一个where子句需要约30秒才能返回。但客户端拥有比我更多的数据。

有任何解决问题的想法吗？

编辑：

根据下面的评论，我重建了索引和统计信息，这提高了前两个where子句的性能，但对第三个没有影响。然后我尝试修改查询，并发现如果我将其重写为：

Select * 
from  dbo.vwSimpleInvoice i 
inner join  
    (Select * from dbo.vwRPTInvoiceLineItemDetail) d on i.InvoiceID = d.InvoiceID 
Where i.InvoiceID in (109581, 109582)

性能回到了预期水平，大约在200毫秒左右。现在我比以往任何时候都更加困惑发生了什么...

编辑2：

实际上，我错了。不是像那样重写查询语句，而是我在重写过程中意外更改了Where子句为：

Where d.InvoiceID in (109581, 109582)

(将i更改为d)。

我仍然有点不明白为什么在内部连接上会产生如此巨大的差异？

---

进一步编辑：

继续尝试，我仍然无法理解。

Select InvoiceId from tblInvoice Where CustomerID = 2000

返回：

80442, 4988, 98497, 102483, 102484, 107958, 127063, 168444, 168531, 173382, 173487, 173633, 174013, 174160, 174240, 175389

Select * from dbo.vwRPTInvoiceLineItemDetail
Where InvoiceID in 
(80442, 4988, 98497, 102483, 102484, 107958, 127063, 168444, 168531, 173382, 173487, 173633, 174013, 174160, 174240, 175389)

运行时间：31 行返回 110 毫秒

Select * from dbo.vwRPTInvoiceLineItemDetail
Where InvoiceID in 
(Select InvoiceId from tblInvoice Where CustomerID = 2000)

运行结果：返回31行，耗时65分钟。

- Molloch

---

执行计划是什么样子？ - DJ Quimby

你尝试过用OR替换那个“IN”运算符吗？比如说“Where d.InvoiceID=109581 OR d.InvoiceID=109582”。这可能会改变执行计划！但是获得帮助的真正方法是发布单个ID与两个ID在WHERE中的执行计划，以便其他人可以分析差异并理解为什么会这样。 - Joe Pineda

我不得不从头开始重写查询。我无法分离出任何导致上述行为的东西。原始查询仍然表现出这种行为，我重写整个树来避免最后的连接。感谢您的帮助。 - Molloch

发票编号在两个表中都是varchar还是INT类型？ - Clark Vera

Answer 1

您遇到的问题（几乎可以确定）是由于一个缓存的查询计划引起的，该计划适用于某些传递给查询的参数版本，但不适用于其他版本（也称为参数嗅探）。

这是一个常见的问题，通常由过时的统计数据和/或碎片化的索引造成的。 第一步：确保您已经重建了所有索引，并且非索引列上的统计数据是最新的。（此外，请确保您的客户端有定期的索引维护作业）

exec sp_msforeachtable "DBCC DBREINDEX('?')"
go

exec sp_msforeachtable "UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS"
go

这是官方参考文献：应用程序缓慢，但在SSMS中快吗？ 如果重建索引和更新统计信息后问题仍然存在，则有几个选项：

1. 使用动态SQL（但请先阅读此文：动态SQL的诅咒与祝福）

2. 使用OPTIMIZE FOR

3. 使用WITH(RECOMPILE)

- Mitch Wheat

---

好的，重建了所有索引和统计信息，并检查了单个InvoiceID的预估执行计划和实际计划，并根据其建议调整了一个索引。现在每个单独的InvoiceID执行时间已经降至约48毫秒，这是一个巨大的改进。但最后一个where子句仍然在运行，直到我点击停止，上次大约花了20分钟。感谢您迄今为止的帮助，还有其他建议吗？ - Molloch

不确定您是否收到通知，但在尝试了您的建议后，我刚刚编辑了我的问题。有什么想法吗？再次感谢。 - Molloch