Combining free text search with another condition is slow

Question

I have a free text catalog on a simple table on SQL Server 2008R2:

CREATE FULLTEXT CATALOG customer_catalog;
CREATE FULLTEXT INDEX ON customer
 ( 
   name1
 ) 
  KEY INDEX customer_pk
  ON customer_catalog;
ALTER FULLTEXT INDEX ON customer START UPDATE POPULATION;

If I perform the following three queries the first two return almost immediately, while the last one takes ~14 seconds on a table with 100,000 records:

SELECT
        customer_id
    FROM
        customer
    WHERE
        CONTAINS(customer.*, 'nomatch');

SELECT
        customer_id
    FROM
        customer
    WHERE
        customer.customer_id = 0;

SELECT
        customer_id
    FROM
        customer
    WHERE
        CONTAINS(customer.*, 'nomatch')
            OR customer.customer_id = 0;

Here are the queryplans:

enter image description here

Why is the third query so much slower? Can I do anything to improve it or do I need to split the query?

Often rewriting `CONTAINS` queries to `CONTAINSTABLE` sorts this type of issue out. [Eg as in this answer](http://stackoverflow.com/questions/2906812/adding-more-or-searches-with-contains-brings-query-to-crawl/2907331#2907331) — Martin Smith, May 11 '13 at 22:09
Depending on your 2008R2 SP version, your problem might be related to following MS Connect issue: http://connect.microsoft.com/SQLServer/feedback/details/520653/full-text-performance-with-mixed-queries — MicSim, May 17 '13 at 14:16
@MicSim: If you make that into an answer, I will accept it. While the other answers has provided nice workarounds, yours look like the real answer. Thanks! — Rasmus Faber, May 17 '13 at 17:36
@RasmusFaber - Just FYI I did my testing on SQL 2012, where the issue that MicSim points out should (in theory) not exist. My queries resulted in index seeks, not index scans like yours. However, the `select` + `union all` combination still gave the best query plan results. I'd be interested to see what your results are after you apply the fix to your SQL Server. — chue x, May 17 '13 at 20:34

chue x · Answer 1 · 2013-05-10T20:59:35.190

It's hard to say why, but it appears SQL Server is choosing an inefficient query plan. Here are some suggestions:

Update the statistics on the table:

UPDATE STATISTICS dbo.customer

Once the statistics are up to date, you could try your queries again and see if there is improvement.

Something else is that for the combined OR statement, SQL Server is using an index scan, instead of a seek. You could try the FORCESEEK hint and see if that makes a difference:

SELECT customer_id
FROM customer WITH (FORCESEEK)
WHERE CONTAINS(customer.*, 'nomatch')
OR customer.customer_id = 0;

One other option, as you mentioned, is to split the statements. The following UNION performs just as well as your first two statements combined:

SELECT customer_id FROM customer
WHERE CONTAINS(customer.*, 'nomatch')

UNION

SELECT customer_id FROM customer
WHERE customer.customer_id = 0

Update - changed above query to UNION instead of UNION ALL.

As @PondLife indicated in the comments, I meant to do a UNION in the above query instead of UNION ALL. After thinking about it, I also tried with UNION ALL and it seemed to be faster. This assumes you don't care about the duplicate IDs:

SELECT customer_id FROM customer
WHERE CONTAINS(customer.*, 'nomatch')

UNION ALL

SELECT customer_id FROM customer
WHERE customer.customer_id = 0

I think you meant `UNION`, not `UNION ALL` (in this specific case), otherwise a row that contains 'nomatch' *and* has ID 0 will appear twice in the result set instead of once. — Pondlife, May 10 '13 at 20:43

score 4 · Answer 2 · answered May 13 '13 at 09:50

The "OR" logicial condition often makes queries run very slowly : / Often, the best option is to use UNION (ALL).

In your case, I'm quite curious about the usage you make of

SELECT
    customer_id
FROM
    customer
WHERE
    customer.customer_id = 0;

It would only result in a list (maybe empty) of zeros. Is it to count (!) how many customer have an id = 0? Is it to check if any customer has an id of 0?

If it's not to count the zeroes but to know if they are any, then this query should be efficient:

SELECT
    customer_id
FROM
    customer
WHERE
    CONTAINS(customer.*, 'nomatch')
    AND customer.customer_id <> 0
UNION ALL
SELECT TOP(1)
    0
FROM
    customer
WHERE
    customer.customer_id = 0

otherwise the efficient query is this one:

SELECT
    customer_id
FROM
    customer
WHERE
    CONTAINS(customer.*, 'nomatch')
    AND customer.customer_id <> 0
UNION ALL
SELECT
    0
FROM
    customer
WHERE
    customer.customer_id = 0

(I just removed the TOP clause)

The `customer_id = 0` was just to show a simple example. The actual query is a join of two tables with a CONTAINS(table1.*) OR CONTAINS(table2.*). But I realized I could reproduce the problem just with a query on the id-column, so I thought that would be a simpler example. — Rasmus Faber, May 13 '13 at 10:41
The condition doesn't matter, use my second query. Start with the part that use the full text index and filter it to exclude the second part (in this example using customer.customer_id <> 0) then make an union all on the second part. — Serge, May 15 '13 at 07:57

score 3 · Accepted Answer · answered May 17 '13 at 21:44

Depending on your MS SQL 2008 R2 sevice pack version, your problem might be related to following Microsoft Connect issue: Full-text performance with "mixed queries"

As per the MS Connect entry, the problem should go away after installing the latest Cumulative Update package for SQL Server 2008 R2.

Combining free text search with another condition is slow

3 Answers3