Geek Answers Handbook

Full-text search optimization across multiple tables

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

The approach I made is shown below. However, this does not seem very effective as the amount of data is growing.

Can anyone suggest 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 of the ideas I reviewed is to create an indexed view, and then search the view. But since these UNIONs are needed for presentation , it is difficult to understand how this will be more efficient.

+5
source share
1 answer

, CONTAINSTABLE FTS . UNION , .

, . . CONTAINSTABLE

 CONTAINSTABLE(AggregatedTable, AggregatedColumn, @FtsQuery)

, 5-10 .

, FTS , . , , , , , , Lucene.

+7

More articles:


All Articles