SQL Server查询超时取决于Where子句 [英] SQL Server Query time out depending on Where Clause

问题描述

我有一个查询，该查询使用3个函数和下面的几个不同视图，这些视图太复杂了，无法在此处发布.我遇到的一件奇怪的事是，在运行顶级查询时，拥有多个搜索键会导致查询运行大约一个小时，而将查询分为两部分则每个查询大约需要5秒钟.

I have a query that uses 3 functions and a few different views beneath it, which are too complex to post here. The odd thing I am experiencing is when running the top level query, having more than 1 search key is causing the query to take around an hour to run, where splitting the query in two takes about 5 seconds per query.

这是顶层查询:

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

当我添加此where子句时:

When I add this where clause:

Where i.InvoiceID = 109581

查询大约需要3秒钟才能运行.类似地，当我添加此where子句时:

The query takes about 3 seconds to run. Similarly when I add this where clause:

Where i.InvoiceID = 109582

大约需要3秒钟.

当我添加此where子句时:

When I add this where clause though:

Where i.InvoiceID in (109581, 109582)

大约50分钟后，我不得不终止该查询，并且它从不返回任何结果.

I have had to kill the query after about 50 minutes, and it never returns any results.

这是在运行SQL Server 2008 R2 Express的远程客户端服务器上发生的.当我在本地运行它(也在SQL Server 2008 R2 Express上)时，没有得到很大的延迟，最后一个where子句需要大约30秒才能返回.客户端的数据比我多得多.

This is occurring on a remote client's server running SQL Server 2008 R2 Express. When I run it locally (also on SQL Server 2008 R2 Express), I don't get the massive delay, the last where clause takes about 30 seconds to return. The client has a lot more data than me though.

有什么想法从哪里开始进行故障排除吗?

Any idea where to start troubleshooting this?

在下面的评论之后，我重建了索引和统计信息，从而改善了前两个where子句的性能，但对第三个where子句没有影响.然后，我处理了该查询，发现如果将其重写为:

After the comments below I rebuilt indexes and stats, which improved performance of the first 2 where clauses, but had no effect on the third. I then played around with the query, and discovered that if I rewrote it as:

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

性能恢复到预期水平，大约200毫秒.现在，我对发生的事情比以往任何时候都更加迷惑了.

Performance returns to expected levels, around 200 ms. I am now more mystified than ever as to what is occurring...

实际上，我错了.不是那样重写查询，而是在重写过程中不小心将Where子句更改为:

Actually, I am wrong. It wasn't rewriting the query like that, I accidentally changed the Where Clause during the rewrite to:

Where d.InvoiceID in (109581, 109582)

(将i更改为d).

对于为什么这样对内部联接产生如此大的差异还是有些茫然?

Still at a bit of a loss as to why this makes such as massive difference on an Inner Join?

进一步

进一步研究它，我仍然听不懂.

Playing around with this even further, I still cannot understand it.

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毫秒

Runs: 31 Rows returned 110 ms

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

运行:31行返回65分钟

Runs: 31 rows returned 65 minutes

推荐答案

(几乎可以肯定)您遇到的问题是由于缓存了查询计划，该计划适用于传递给查询的某些版本的参数，但不适用于其他(又称为参数嗅探).

The Problem you are experiencing is (almost certainly) due to a cached query plan, which is appropriate for some version of parameters passed to the query, but not for others (aka Parameter Sniffing).

这是很常见的情况，由于过时的统计信息和/或索引碎片严重，常常使情况变得更糟.

This is a common occurance, and is often made worse by out of date statistics and/or badly fragmented indexes.

第一步:确保您已重建所有索引，并且未索引列的统计信息是最新的. (此外，请确保您的客户有定期安排的索引维护作业)

First step: ensure you have rebuilt all your indexes and that statistics on non-indexed columns are up to date. (Also, make sure your client has a regularly scheduled index maintenance job)

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

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

这是规范参考:应用程序中的速度慢，SSMS中的速度快吗?

如果在重建索引和更新统计信息之后问题仍然存在，那么您有几个选择:

If the problem still exists after rebuilding indexes and updating statistics, then you have a few options:

1. 使用动态SQL(但请先阅读以下内容: 动态SQL )

1. Use dynamic SQL (but read this first: The Curse and Blessings of Dynamic SQL)

使用使用WITH(RECOMPILE)

这篇关于SQL Server查询超时取决于Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！