如何强制SQL Server在WHERE子句之前处理CONTAINS子句？

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.

我有一个SQL查询,它使用标准的WHERE子句和全文索引CONTAINS子句。查询是从代码动态构建的,包含可变数量的WHERE和CONTAINS子句。

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.

但是,SQL Server选择在CONTAINS子句之前处理WHERE子句,这会导致表扫描并且查询非常慢。

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.

我可以使用两个查询和一个临时表重写它。当我这样做时,查询执行速度提高了10倍。但我不想在创建查询的代码中这样做,因为它太复杂了。

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.

有没有办法强制SQL Server在其他任何事情之前处理CONTAINS?我无法强制执行计划(USE PLAN),因为查询是动态构建的并且变化很大。

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

注意:我在SQL Server 2005和SQL Server 2008上遇到同样的问题。

3 个解决方案

#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.

但是,SQL是声明性的:你说出你想要的,而不是如何去做。因此,优化器可能会决定忽略上面的嵌套。

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

在应用经典WHERE子句之前,可以强制使用CONTAINS实现派生表。我不保证表现。

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

#2

Try doing it with 2 queries without temp tables:

尝试使用没有临时表的2个查询:

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

#3

As I noted above, this is NOT as clean a way to "materialize" the derived table as the TOP clause that @gbn proposed, but a loop join hint forces an order of evaluation, and has worked for me in the past (admittedly usually with two different tables involved). There are a couple of problems though:

正如我上面提到的,这并不像@gbn提出的那样将实体表“物化”为一种干净的方式,但是循环连接提示会强制执行评估顺序,并且在过去对我有用(当然通常是涉及两个不同的表)。但是有几个问题:

The query is ugly

查询很难看

you still don't get any guarantees that the other WHERE parameters don't get evaluated until after the join (I'll be interested to see what you get)

你仍然没有得到任何保证,其他WHERE参数直到加入后才得到评估(我有兴趣看看你得到了什么)

Here it is though, 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

#1