为什么SQL FullText查询减慢当您的OR？

发布时间：2020-12-12 08:42:52 所属栏目：MsSql教程来源：网络整理

导读：在SQL Server(2008)中,我有两列的FullText索引,称为Table1.FirstNames和Table2.LastNames.在分析了一些查询后,我想出了以下结果： SELECT *FROM (Table1 LEFT JOIN Table2 ON Table1.SomeKey=Table2.SomeKey)WHERE CONTAINS(FirstNames,'Bob') OR CONTAINS(La

在SQL Server(2008)中,我有两列的FullText索引,称为Table1.FirstNames和Table2.LastNames.在分析了一些查询后,我想出了以下结果：

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

=> 31 197ms

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

=> 1941ms

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

=> 3201ms

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

=> 565ms

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

=> 670ms

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

=> 17MS

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

=>为3ms

即使重建FullText索引,此行为仍然存在.

FullText通常比用特定语言的大量数据的LIKE查询快得多,但是为什么当我将两个FullText文件夹在一起时,为什么查询速度会减慢一个数量级？

解决方法

改变为使用ContainsTable帮助？

这是在Adding more OR searches with CONTAINS Brings Query to Crawl

同样的回答者(Joe Stefanelli)通过将FREETEXT谓词与OR结合到一个FREETEXTTABLE来实现类似的改进SQL Server full text query across multiple tables – why so slow?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!