为什么SQL全文查询在您或?

In SQL Server (2008), I have a FullText index on two columns, call them Table1.FirstNames and Table2.LastNames. After profiling some queries, I came up with the following results:

在SQL Server(2008)中，我在两列上有一个全文索引，称为Table1。firstname和Table2.LastNames。在分析了一些查询之后，我得出了以下结果:

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob') OR CONTAINS(LastNames, 'Bob')

=> 31 197ms

= > 31 197 ms

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE (FirstNames LIKE '%Bob%') OR CONTAINS(LastNames, 'Bob')

=> 1941ms

= > 1941毫秒

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob') OR LastNames LIKE '%Bob%'

=> 3201ms

= > 3201毫秒

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(FirstNames, 'Bob')

=> 565ms

= > 565毫秒

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE FirstNames LIKE '%Bob%'

=> 670ms

= > 670毫秒

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE CONTAINS(LastNames, 'Bob')

=> 17ms

= > 17女士

SELECT *
FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)
WHERE LastNames LIKE '%Bob%'

=> 3ms

= > 3女士

This behaviour persists even if I rebuild the FullText index.

即使我重新构建全文索引，这种行为仍然存在。

FullText is usually much faster than a LIKE query over large sets of data in a specific language, but why do query speeds slow down by an order of magnitude when I OR together two FullText clasues?

在特定语言中，FullText通常比类似于大型数据集的查询快得多，但是为什么当我或一起使用两个FullText clasues时，查询速度会以数量级的速度慢下来呢?

3 个解决方案

#1

Does changing to using ContainsTable help?

更改为使用容器稳定是否有帮助?

It did here Adding more OR searches with CONTAINS Brings Query to Crawl

它在这里添加了更多或包含的搜索带来了查询

And the same answerer (Joe Stefanelli) managed to bring about a similar improvement by changing FREETEXT predicates combined with OR to a FREETEXTTABLE here SQL Server full text query across multiple tables - why so slow?

同样的应答者(Joe Stefanelli)通过改变FREETEXT谓词组合或将FREETEXT谓词改为FREETEXTTABLE SQL Server全文本查询跨多个表——为什么这么慢?

#2

Perhaps you should read this: SQL Server 2005 Full-Text Queries on Large Catalogs: Lessons Learned.

也许您应该读一下:SQL Server 2005全文查询大目录:经验教训。

#3

I'd take a look at the execution plan for each of those. I'm guessing you will learn quite a bit from that.

我来看看每个人的执行计划。我猜你会从中学到很多。

Here is a decent link that will show you how to display the execution plan as well as some tips on interpreting it.

这里有一个不错的链接，将向您展示如何显示执行计划，以及一些解释它的技巧。

#1