Why is my request so slow? (SQL Server 2008 full-text search for weirdness)

I have a table with a full-text indexed column MiddlePart. The table has about 600,000 rows. The following query is very fast (30 results, <1 second):

select * from DomainName
where contains (MiddlePart, '"antiques*"')
    OR freetext(MiddlePart, 'antiques')

This query is also very fast (5 results, <1 second):

select * from DomainName
where (contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
    AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training'))

So why do these queries EXCEED more slowly? (90 seconds + and I canceled the request):

Request A:

select * from DomainName
where contains (MiddlePart, '"antiques*"')
    OR freetext(MiddlePart, 'antiques')
union
select * from DomainName
where (contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
    AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training'))

Request B:

select * from DomainName
where (contains (MiddlePart, '"antiques*"')
    OR freetext(MiddlePart, 'antiques'))
OR
    ((contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
    AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training')))

EDIT

Full text plan for QUERY A:

|--Merge Join(Union)
   |--Nested Loops(Inner Join, OUTER REFERENCES:(FulltextMatch.[docid], [Expr1055]) WITH ORDERED PREFETCH)
   |    |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]))
   |    |    |--Merge Join(Concatenation)
   |    |         |--Table-valued function
   |    |         |--Table-valued function
   |    |--Clustered Index Seek(OBJECT:([domaining].[dbo].[DomainName].[PK__DomainNa__3214EC2708EA5793]), SEEK:([domaining].[dbo].[DomainName].[ID]=FulltextMatch.[docid]) ORDERED FORWARD)
   |--Merge Join(Left Semi Join, MERGE:([domaining].[dbo].[DomainName].[ID])=(FulltextMatch.[docid]), RESIDUAL:([domaining].[dbo].[DomainName].[ID]=FulltextMatch.[docid]))
        |--Nested Loops(Inner Join, OUTER REFERENCES:(FulltextMatch.[docid], [Expr1056]) WITH ORDERED PREFETCH)
        |    |--Stream Aggregate(GROUP BY:(FulltextMatch.[docid]))
        |    |    |--Merge Join(Concatenation)
        |    |         |--Table-valued function
        |    |         |--Table-valued function
        |    |--Clustered Index Seek(OBJECT:([domaining].[dbo].[DomainName].[PK__DomainNa__3214EC2708EA5793]), SEEK:([domaining].[dbo].[DomainName].[ID]=FulltextMatch.[docid]) ORDERED FORWARD)
        |--Merge Join(Concatenation)
             |--Table-valued function
             |--Table-valued function

Full text plan for request B:

|--Nested Loops(Left Semi Join, OUTER REFERENCES:([domaining].[dbo].[DomainName].[ID]))
   |--Clustered Index Scan(OBJECT:([domaining].[dbo].[DomainName].[PK__DomainNa__3214EC2708EA5793]))
   |--Concatenation
        |--Table-valued function
        |--Nested Loops(Left Semi Join)
        |    |--Concatenation
        |    |    |--Table-valued function
        |    |    |--Table-valued function
        |    |--Row Count Spool
        |         |--Concatenation
        |              |--Table-valued function
        |              |--Table-valued function
        |--Table-valued function
+3
source share
1 answer

SHOWPLAN , , , . , , .

, , :

select * into #a from DomainName
where contains (MiddlePart, '"antiques*"')
    OR freetext(MiddlePart, 'antiques')

select * into #b from DomainName
where (contains (MiddlePart, '"dog*"') OR freetext(MiddlePart, 'dog'))
    AND (contains (MiddlePart, '"training*"') OR freetext(MiddlePart, 'training'))

select * from #a union #b
+2

Source: https://habr.com/ru/post/1735621/


All Articles