为什么多个 WHERE 条件会减慢查询速度而不是加快查询速度? [英] Why do multiple WHERE conditions slow query rather than speed it up?

问题描述

问题在于，与使用一两个条件而不是所有三个条件运行的查询相比，相关查询的运行速度非常慢.

The problem is that the query in question runs very slow when compared to the query run with one or two, rather than all three of its conditions.

现在是查询.

Select Count(*)
From 
    SearchTable 
Where 
    [Date] >= '8/1/2009' 
    AND 
    [Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))
    AND 
    FreeText([Description], 'keyword list here')  

第一个条件是不言自明的.第二个使用 UDF 获取 30348 150 英里范围内的邮政编码列表.第三个使用全文索引来搜索提供的词.

The first condition is self explanatory. The second uses a UDF to get a list of Zip Codes within 150 miles of 30348. The third uses a full text index to search for the provided words.

只有这个条件

[Date] >= '8/1/2009' 

查询在 3 秒内返回 43884(表大小不到 500k 行).

The query returns 43884 (table size is just under 500k rows) in 3 seconds.

只使用这个条件

[Zip] In (Select ZipCode from dbo.ZipCodesForRadius('30348', 150))

我得到 27920，也在 3 秒内返回.

I get 27920, also returned in 3 seconds.

而且只有全文部分

FreeText([Description], 'keyword list here')

68404 在 8 秒后返回.

68404 is returned in 8 seconds.

当我只使用邮政编码和全文条件时，我在 4 秒内得到 4919.
仅日期和全文条件就让我在不到 14 秒的时间内得到 9481.
使用日期和邮政编码条件只能在 14 秒内给我 3238.
在所有三个条件下，查询都会在 2 分 53 秒内返回 723.(wtfbbq)

When I use just the zip code and full text conditions I get 4919 in 4 seconds.
Just the date and full text conditions gets me 9481 in just shy of 14 seconds.
Using the date and Zip Code conditions only gives me 3238 in 14 seconds.
With all three conditions the query returns 723 in 2 minutes, 53 seconds. (wtfbbq)

推荐答案

了解原因的唯一方法是检查执行计划.尝试SET SHOWPLAN_TEXT ON.

The only way to know why is to check the execution plan. Try SET SHOWPLAN_TEXT ON.

这篇关于为什么多个 WHERE 条件会减慢查询速度而不是加快查询速度?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！