使用CONTAINS添加更多OR搜索将查询引入抓取 [英] Adding more OR searches with CONTAINS Brings Query to Crawl

问题描述

我有一个简单的查询依赖于两个全文索引表，但当我将 CONTAINS 与任何其他 OR 搜索结合使用时，运行速度非常缓慢。正如执行计划中所看到的那样，这两个全文搜索会压缩性能。如果我仅用CONTAINS中的一个查询，或者两者都不查询，查询就是秒，但是当您将 OR 添加到混合中时，查询就会变得不合时宜。

这两个表格并没有什么特别的，它们并不太宽（一个42列，另一个21个;可能10列是FT索引），甚至包含非常多的记录（36k recs在两者中最大的一个）。

我通过将两个 CONTAINS 搜索拆分到他们自己的 SELECT 查询，然后再将 UNION 三者结合在一起。这是UNION解决方法我唯一的希望吗？

谢谢。

  SELECT a.CollectionID 
从集合a 
 INNER JOIN确定b ON a.CollectionID = b.CollectionID 
 WHERE a.CollrTeam_Text LIKE'％fa％'
或CONTAINS（a。* ，''* fa *''）
或CONTAINS（b。*，'* fa *'）
  

执行计划（在我发布图像之前，我需要更多声望）： http://yfrog.com/7dslowcontainsj http://desmond.yfrog.com/Himg265/scaled.php?tn=0&ser​​ver=265& filename = slowcontains.jpg& xsize = 640& ysize = 640

解决方案

一个LEFT JOIN到一个相当于CONTAINSTABLE的表现会更好。例如：

  SELECT a.CollectionID 
从集合a 
 INNER JOIN确定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 
  

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.

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).

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?

Thanks.

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*"')

Execution Plan (guess I need more reputation before I can post the image): http://yfrog.com/7dslowcontainsj http://desmond.yfrog.com/Himg265/scaled.php?tn=0&server=265&filename=slowcontains.jpg&xsize=640&ysize=640

解决方案

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屋！