views:

449

answers:

1

I have a table with a full-text indexed column MiddlePart. The table has around 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 are BOTH these queries EXCEEDINGLY slow? (90seconds+ and I cancelled the query):

Query 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'))

Query 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 Query 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
+1  A: 

Since you didn't include SHOWPLAN for the combined query, I would guess it didn't output one, which would point to a bug in the optimizer. This has been known to happen in other places.

In any case, temp tables are always a respectable choice when faced with such strangeness:

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
Peter