为什么在您进行 OR 时 SQL FullText 查询会变慢? [英] Why do SQL FullText queries slow down when you OR?

问题描述

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

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:

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

=> 31 197 毫秒

=> 31 197ms

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

=> 1941 毫秒

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

=> 3201 毫秒

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

=> 565 毫秒

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

=> 670 毫秒

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

=> 17 毫秒

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

=> 3 毫秒

即使我重建 FullText 索引，这种行为仍然存在.

This behaviour persists even if I rebuild the FullText index.

FullText 通常比对特定语言的大量数据进行 LIKE 查询快得多，但是为什么当我将两个 FullText 子句 OR 在一起时，查询速度会降低一个数量级?

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?

推荐答案

改为使用 ContainsTable 有帮助吗?

Does changing to using ContainsTable help?

在这里添加更多OR 使用 CONTAINS 进行搜索将查询带入抓取

同一个回答者 (Joe Stefanelli) 通过更改 FREETEXT 设法带来了类似的改进 谓词与 OR 结合到 FREETEXTTABLE 此处 跨多个表的 SQL Server 全文查询 - 为什么这么慢?

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?

这篇关于为什么在您进行 OR 时 SQL FullText 查询会变慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！