SQL Server查询添加通配符(或顶部)的速度大幅放缓

Question

我有一个有2000万只动物的动物园，我在我的Server 2005数据库中跟踪了这些动物。其中约1%为黑色，约1%为天鹅。我想了解所有黑天鹅的详细情况，所以，我不想淹没结果页，我做了：

select top 10 * 
from animal 
where colour like 'black'  
and species like 'swan'

(是的，无意中，这些字段都是freetext，但它们都是索引的)。结果，我们没有这样的动物，因为查询在大约300毫秒内返回一个空集。如果我用“=”而不是“喜欢”的话，它的速度大约是原来的两倍，但我有预感，后者将为我节省一些打字时间。

原来，动物园管理员认为他可能输入了一些天鹅为“黑色”，因此我相应地修改了这个查询：

select top 10 * 
from animal  
where colour like 'black%' 
and species like 'swan'

结果也没有这些动物(事实上，除了‘黑色’动物之外，没有‘黑%’动物)，但是这个查询现在需要大约30秒才能返回空的。

不过，似乎只有“top”和“like%”的组合才会引起麻烦，因为

select count(*) 
from animal  
where colour like 'black%' 
and species like 'swan'

非常快地返回0，甚至

select * 
from animal 
where colour like 'black%' 
and species like 'swan'

在一秒钟内返回空值。

有没有人知道为什么'top‘和'%’应该合谋导致如此巨大的性能损失，特别是在一个空的结果集中？

编辑:为了澄清，我没有使用任何FreeText索引，我的意思是字段在入口点是自由的，即在数据库中没有规范化。抱歉，我的措辞不太好。

Answer 1

建立这个有趣的，我做了一些搜索和偶然发现了这个Q/A 顶层是如何(以及为什么)影响执行计划的？

基本上，使用TOP会改变跟随它的操作符的成本(以一种非平凡的方式)，这会导致总体计划也会发生变化(如果您包括了ExecPlans (包括和不包括前10位)，这将很大程度上改变查询的总体执行。

希望这能有所帮助。

例如，我在一个数据库上尝试过这样的方法：-when不调用TOP，并行使用-with top，不使用并行性。

因此，再次显示您的执行计划将提供更多的信息。

祝您今天愉快

Answer 2

我相信这可能是由于MSSQL 2005的底层特性以及查询优化器决定哪个执行计划是最有效的。

如果使用SQL变量，它应该“欺骗”查询优化器使用哈希匹配，而不是嵌套循环，这将导致更高程度的并行性。

尝试：

DECLARE @topn INT = 10
SELECT TOP (@topn) *
FROM    animal
WHERE   colour LIKE 'black%' 
AND species LIKE 'swan'