跨多个表优化全文搜索 [英] Optimize Full-Text Search Across Multiple Tables

问题描述

我有要求在我的SQL Server数据库中搜索几个不同的表。我需要根据在哪个表中匹配结果对结果进行排序。

我采取的方法如下所示。然而，随着数据量的增长，这看起来效率不高。

任何人都可以提出任何技巧来优化它吗？

   - 全文查询
 DECLARE @FtsQuery nvarchar（100）
 SET @FtsQuery ='FORMSOF（INFLECTIONAL，detail）'
 
  - 描述栏中的最大字符数
 DECLARE @MaxDescription int 
 SET @MaxDescription = 250 
 
 SELECT 1 AS RankGroup，FTS.Rank，Id，Title， LEFT（[Description]，@MaxDescription）AS描述FROM Table1 
 INNER JOIN CONTAINSTABLE（Table1，*，@FtsQuery）AS FTS ON FTS。[KEY] = Table1.Id 
 UNION SELECT 2，FTS。等级，Id，标题，NULL从表2 
 INNER JOIN CONTAINSTABLE（Table2，*，@FtsQuery）AS FTS ON FTS。[KEY] = Table2.Id 
 UNION SELECT 3，FTS.Rank， Title，LEFT（[Description]，@MaxDescription）FROM Table3 
 INNER JOIN CONTAINSTABLE（Table3，*，@FtsQuery）AS FTS ON FTS。[KEY] = Table3.Id 
 UNION SELECT 4，FTS。 Rank，Id，Title，LEFT（[说明]，@MaxDe （表4，*，@FtsQuery）AS FTS ON FTS。[KEY] = Table4.Id 
 UNION SELECT 5，FTS.Rank，Id，Title，LEFT（[描述]，@MaxDescription）FROM Table5 
 INNER JOIN CONTAINSTABLE（Table5，*，@FtsQuery）AS FTS ON FTS。[KEY] = Table5.Id 
 ORDER BY RankGroup，Rank DESC 
  

我想过的一个想法是创建一个索引视图，然后在视图上执行搜索。但由于该视图需要这些 UNION s，所以很难看出这会更有效。

解决方案

这是一个难题，因为CONTAINSTABLE一次只能搜索单个表的FTS索引。只要您的表现可以接受，您的UNION解决方案就没有问题。

我们面临同样的问题，即需要在单个查询中从多个表中高效地搜索许多列。我们所做的是将这些列和表中的所有数据汇总到一个只读表中。然后，我们的查询只需要一个CONTAINSTABLE调用

  CONTAINSTABLE（AggregatedTable，AggregatedColumn，@FtsQuery）
  

我们有一个计划任务，每5-10分钟运行一次，并将来自我们源表的所有修改内容逐步累加到我们的单一只读聚合内容表。

一般来说，在任何合理大小的数据库和用户负载中使用FTS似乎意味着您始终与性能相悖。如果你发现无论你做什么你都无法让表现被接受，你可能需要调查其他技术，例如
Lucene 。

I have the requirement to search several different tables in my SQL Server database. And I need to sort the results based on in which table the match occurred.

The approach I've taken is shown below. However, this doesn't seem very efficient as the amount of data grows.

Can anyone suggests any tricks to optimize this?

-- Full-text query
DECLARE @FtsQuery nvarchar(100)
SET @FtsQuery = 'FORMSOF(INFLECTIONAL, detail)'

-- Maximum characters in description column
DECLARE @MaxDescription int
SET @MaxDescription = 250

SELECT 1 AS RankGroup, FTS.Rank, Id, Title, LEFT([Description], @MaxDescription) AS Description FROM Table1
    INNER JOIN CONTAINSTABLE(Table1, *, @FtsQuery) AS FTS ON FTS.[KEY] = Table1.Id
UNION SELECT 2, FTS.Rank, Id, Title, NULL FROM Table2
    INNER JOIN CONTAINSTABLE(Table2, *, @FtsQuery) AS FTS ON FTS.[KEY] = Table2.Id
UNION SELECT 3, FTS.Rank, Id, Title, LEFT([Description], @MaxDescription) FROM Table3
    INNER JOIN CONTAINSTABLE(Table3, *, @FtsQuery) AS FTS ON FTS.[KEY] = Table3.Id
UNION SELECT 4, FTS.Rank, Id, Title, LEFT([Description], @MaxDescription) FROM Table4
    INNER JOIN CONTAINSTABLE(Table4, *, @FtsQuery) AS FTS ON FTS.[KEY] = Table4.Id
UNION SELECT 5, FTS.Rank, Id, Title, LEFT([Description], @MaxDescription) FROM Table5
    INNER JOIN CONTAINSTABLE(Table5, *, @FtsQuery) AS FTS ON FTS.[KEY] = Table5.Id
ORDER BY RankGroup, Rank DESC

One idea I'd considered is to create an indexed view and then perform the search on the view. But since the view would need these UNIONs, it's hard to see how that would be any more efficient.

解决方案

This is a difficult issue, because CONTAINSTABLE can only search a single table's FTS index at a time. Your UNION solution above is fine as long as your performance is acceptable.

We faced the same issue of needing to efficiently search many columns from many tables in a single query. What we did was aggregate all of the data from these columns and tables into a single read-only table. Our query then only needed a single CONTAINSTABLE call

 CONTAINSTABLE(AggregatedTable, AggregatedColumn, @FtsQuery)

We have a scheduled job that runs every 5-10 minutes and incrementally aggregates any modified content from our source table into our single read-only aggregated content table.

In general it seems that using FTS in any reasonably-sized database and user load means you are always battling with performance. If you find that no matter what you do you cannot get the performance to be acceptable, you may need to investigate other technologies such as Lucene.

这篇关于跨多个表优化全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！