在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?
