跨多个表优化全文搜索 [英] 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 UNION
s, 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屋!