How to get SQL Server to process CONTAINS statements before WHERE statements?

Question

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

For a query to be fast, it is very important that the entire text index is executed before the other criteria are applied.

However, SQL Server decides to process the WHERE clauses before the CONTAINS clauses and invokes table scans, and the query is very slow.

I can rewrite this using two queries and a temporary table. When I do this, the query is 10 times faster. But I do not want to do this in the code that creates the request, because it is too complicated.

Is there a way to get SQL Server to process CONTAINS first? I cannot force a plan (USER PLAN) because the request is built dynamically and varies greatly.

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

Answer 1

You can communicate your intent to an optimizer 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. Therefore, the optimizer may decide to ignore the nesting above.

You can force the CONTAINS view to materialize before applying the classic WHERE clause. I will not guarantee performance.

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

Answer 2

Try this with two queries without temporary tables:

 SELECT * FROM table WHERE id IN ( SELECT id FROM table WHERE contains_criterias ) AND further_where_classes 

Answer 3

As I noted above, this is NOT a clean way to “materialize” the view as the TOP sentence offered by @gbn, but the loop join prompt forces the evaluation order and worked for me in the past (admittedly, usually involving two different tables) . However, there are several problems:

• Ugly request
• you still do not get any guarantees that other WHERE parameters will not be evaluated until you join (I will be interested to know what you get)

Here, however, given that you asked:

 SELECT OriginalTable.XXX FROM ( SELECT XXX FROM OriginalTable WHERE CONTAINS XXX ) AS ContainsCheck INNER LOOP JOIN OriginalTable ON ContainsCheck.PrimaryKeyColumns = OriginalTable.PrimaryKeyColumns AND OriginalTable.OtherWhereConditions = OtherValues