How to force SQL Server to process CONTAINS clauses before WHERE clauses?

Question

I have a SQL query that uses both standard WHERE clauses and full text index CONTAINS clauses. The query is built dynamically from code and includes a variable number of WHERE and CONTAINS clauses.

In order for the query to be fast, it is very important that the full text index be searched before the rest of the criteria are applied.

However, SQL Server chooses to process the WHERE clauses before the CONTAINS clauses and that causes tables scans and the query is very slow.

I'm able to rewrite this using two queries and a temporary table. When I do so, the query executes 10 times faster. But I don't want to do that in the code that creates the query because it is too complex.

Is there an a way to force SQL Server to process the CONTAINS before anything else? I can't force a plan (USE PLAN) because the query is built dynamically and varies a lot.

Note: I have the same problem on SQL Server 2005 and SQL Server 2008.

Answer 1

You can signal your intent to the optimiser like this

SELECT
   *
FROM
    (
    SELECT *
    FROM


    WHERE
       CONTAINS
    ) T1
WHERE
   (normal conditions)

However, SQL is declarative: you say what you want, not how to do it. So the optimiser may decide to ignore the nesting above.

You can force the derived table with CONTAINS to be materialised before the classic WHERE clause is applied. I won't guarantee performance.

SELECT
   *
FROM
    (
    SELECT TOP 2000000000
       *
    FROM
       ....
    WHERE
       CONTAINS
    ORDER BY
       SomeID
    ) T1
WHERE
   (normal conditions)