如何使用CONTAINS添加更多OR搜索使查询爬网? [英] How to add more OR searches with CONTAINS Brings Query to Crawl?

问题描述

我有一个简单的查询，该查询依赖于两个全文索引表，但是当我将 CONTAINS 与任何其他的 OR 搜索结合使用时，它的运行速度将非常慢.从执行计划中可以看出，这两个全文搜索会降低性能.如果我只查询其中一个CONTAINS，或者都不查询，则查询时间不到一秒，但是当您在混音中添加 OR 时，查询就会失败.

I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the CONTAINS combined with any additional OR search. As seen in the execution plan, the two full text searches crush the performance. If I query with just 1 of the CONTAINS, or neither, the query is sub-second, but the moment you add OR into the mix the query becomes ill-fated.

这两个表没什么特别的，它们不是太宽(一个表中有42个列，另一个中有21个列；每个FT索引中有10个列)，甚至包含非常多的记录(最大的36k个记录).两个).

The two tables are nothing special, they're not overly wide (42 cols in one, 21 in the other; maybe 10 cols are FT indexed in each) or even contain very many records (36k recs in the biggest of the two).

我能够通过将两个 CONTAINS 搜索分为自己的 SELECT 查询，然后将三个 UNION 组合在一起来解决性能问题.我唯一的希望就是这个UNION解决方法吗?

I was able to solve the performance by splitting the two CONTAINS searches into their own SELECT queries and then UNION the three together. Is this UNION workaround my only hope?

SELECT     a.CollectionID
FROM       collections    a
INNER JOIN determinations b ON a.CollectionID = b.CollectionID 
WHERE      a.CollrTeam_Text LIKE '%fa%'
           OR CONTAINS(a.*, '"*fa*"')
           OR CONTAINS(b.*, '"*fa*"')

执行计划:

推荐答案

我很好奇，看看向等效CONTAINSTABLE的LEFT JOIN是否会有更好的表现.像这样:

I'd be curious to see if a LEFT JOIN to an equivalent CONTAINSTABLE would perform any better. Something like:

SELECT     a.CollectionID
FROM       collections    a
INNER JOIN determinations b ON a.CollectionID = b.CollectionID 
LEFT JOIN CONTAINSTABLE(a, *, '"*fa*"') ct1 on a.CollectionID = ct1.[Key]
LEFT JOIN CONTAINSTABLE(b, *, '"*fa*"') ct2 on b.CollectionID = ct2.[Key]
WHERE      a.CollrTeam_Text LIKE '%fa%'
           OR ct1.[Key] IS NOT NULL
           OR ct2.[Key] IS NOT NULL

这篇关于如何使用CONTAINS添加更多OR搜索使查询爬网?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！