SQL 2008：在不需要时阻止查询中的全文查找(SQL 2008: Prevent Full-text lookup in query when not needed)

从这个特定的情况来看，对我来说，逻辑运算符不会在SQL中短路。

我经常在where子句中执行这些操作（通常在处理搜索查询时）：

WHERE (@Description IS NULL OR @Description = myTable.Description)

即使它在这个例子中没有被短路，这并不重要。 但是，在处理全文搜索功能时，它确实很重要。如果该查询的第二部分是CONTAINS(myTable.Description, @Description) ，它将无法工作，因为该变量不允许为null或为空这些功能。

我发现CASE的WHEN语句是按顺序执行的，所以我可以改变我的查询，以确保只在需要时调用全文查找，同时将变量从null更改为'""'当它为null时允许要执行的查询：

WHERE (CASE WHEN @Description = '""' THEN 1 WHEN CONTAINS(myTable.Description, @Description) THEN 1 ELSE 0 END = 1)

上面的代码应该阻止执行全文查询，除非实际有一个值要搜索。

我的问题是，如果我运行这个查询，其中@Description是'""' ，那么在处理聚簇索引搜索和fulltextmatch的执行计划中仍然有相当多的时间，即使该表和搜索最终没有完全使用：有什么办法可以避免这种情况吗？

我试图从硬编码的动态查询和存储过程中得到这个，但如果程序最终变慢，我不确定我能证明它是正确的。

From working on this specific situation, it was news to me that the logic operators are not short circuited in SQL.

I routinely do something along these lines in the where clause (usually when dealing with search queries):

WHERE (@Description IS NULL OR @Description = myTable.Description)

Which, even if it's not short-circuited in this example, doesn't really matter. However, when dealing with the fulltext search functions, it does matter.. If the second part of that query was CONTAINS(myTable.Description, @Description), it wouldn't work because the variable is not allowed to be null or empty for these functions.

I found out the WHEN statements of CASE are executed in order, so I can change my query like so to ensure the fulltext lookup is only called when needed, along with changing the variable from null to '""' when it is null to allow the query to execute:

WHERE (CASE WHEN @Description = '""' THEN 1 WHEN CONTAINS(myTable.Description, @Description) THEN 1 ELSE 0 END = 1)

The above code should prevent the full-text query piece from executing unless there is actually a value to search with.

My question is, if I run this query where @Description is '""', there is still quite a bit of time in the execution plan spent dealing with clustered index seeks and fulltextmatch, even though that table and search does not end up being used at all: is there any way to avoid this?

I'm trying to get this out of a hardcoded dynamic query and into a stored procedure, but if the procedure ends up being slower, I'm not sure I can justify it.

最满意答案

这不是理想的，但也许这样的事情会起作用：

IF @Description = '' BEGIN SELECT ... END ELSE BEGIN SELECT ... WHERE CONTAINS(mytable.description, @Description) END

这样你就可以避免使用mysql，也可以在不需要时运行FT扫描。

作为一些一般性说明，我通常发现CONTAINSTABLE要快一点。 此外，由于无论您使用的是我的解决方案还是您的解决方案，查询计划都将大不相同，请注意参数嗅探 。 参数嗅探是指优化程序根据传入的特定参数值构建计划的时间。

In case anyone else runs into a scenario like this, this is what I ended up doing, which is pretty close to what M_M was getting at; I broke away the full-text pieces and placed them behind branches:

DECLARE @TableBfullSearch TABLE (TableAId int) IF(@TableBSearchInfo IS NOT NULL) INSERT INTO @TableBfullSearch SELECT TableAId FROM TableB WHERE ...(fulltext search)... DECLARE @TableCfullSearch TABLE (TableAId int) IF(@TableCSearchInfo IS NOT NULL) INSERT INTO @TableCfullSearch SELECT TableAId FROM TableC WHERE ...(fulltext search)... --main query with this addition in the where clause SELECT ... FROM TableA WHERE ... AND (@TableBSearchInfo IS NULL OR TableAId IN (SELECT TableAId FROM @TableBfullSearch)) AND (@TableCSearchInfo IS NULL OR TableAId IN (SELECT TableAId FROM @TableCfullSearch))

I think that's probably about as good as it'll get without some sort of dynamic query

SQL 2008：在不需要时阻止查询中的全文查找(SQL 2008: Prevent Full-text lookup in query when not needed)

从这个特定的情况来看，对我来说，逻辑运算符不会在SQL中短路。

我经常在where子句中执行这些操作（通常在处理搜索查询时）：

WHERE (@Description IS NULL OR @Description = myTable.Description)

即使它在这个例子中没有被短路，这并不重要。 但是，在处理全文搜索功能时，它确实很重要。如果该查询的第二部分是CONTAINS(myTable.Description, @Description) ，它将无法工作，因为该变量不允许为null或为空这些功能。

我发现CASE的WHEN语句是按顺序执行的，所以我可以改变我的查询，以确保只在需要时调用全文查找，同时将变量从null更改为'""'当它为null时允许要执行的查询：

WHERE (CASE WHEN @Description = '""' THEN 1 WHEN CONTAINS(myTable.Description, @Description) THEN 1 ELSE 0 END = 1)

上面的代码应该阻止执行全文查询，除非实际有一个值要搜索。

我的问题是，如果我运行这个查询，其中@Description是'""' ，那么在处理聚簇索引搜索和fulltextmatch的执行计划中仍然有相当多的时间，即使该表和搜索最终没有完全使用：有什么办法可以避免这种情况吗？

我试图从硬编码的动态查询和存储过程中得到这个，但如果程序最终变慢，我不确定我能证明它是正确的。

From working on this specific situation, it was news to me that the logic operators are not short circuited in SQL.

I routinely do something along these lines in the where clause (usually when dealing with search queries):

WHERE (@Description IS NULL OR @Description = myTable.Description)

Which, even if it's not short-circuited in this example, doesn't really matter. However, when dealing with the fulltext search functions, it does matter.. If the second part of that query was CONTAINS(myTable.Description, @Description), it wouldn't work because the variable is not allowed to be null or empty for these functions.

I found out the WHEN statements of CASE are executed in order, so I can change my query like so to ensure the fulltext lookup is only called when needed, along with changing the variable from null to '""' when it is null to allow the query to execute:

WHERE (CASE WHEN @Description = '""' THEN 1 WHEN CONTAINS(myTable.Description, @Description) THEN 1 ELSE 0 END = 1)

The above code should prevent the full-text query piece from executing unless there is actually a value to search with.

My question is, if I run this query where @Description is '""', there is still quite a bit of time in the execution plan spent dealing with clustered index seeks and fulltextmatch, even though that table and search does not end up being used at all: is there any way to avoid this?

I'm trying to get this out of a hardcoded dynamic query and into a stored procedure, but if the procedure ends up being slower, I'm not sure I can justify it.

最满意答案

这不是理想的，但也许这样的事情会起作用：

IF @Description = '' BEGIN SELECT ... END ELSE BEGIN SELECT ... WHERE CONTAINS(mytable.description, @Description) END

这样你就可以避免使用mysql，也可以在不需要时运行FT扫描。

作为一些一般性说明，我通常发现CONTAINSTABLE要快一点。 此外，由于无论您使用的是我的解决方案还是您的解决方案，查询计划都将大不相同，请注意参数嗅探 。 参数嗅探是指优化程序根据传入的特定参数值构建计划的时间。

In case anyone else runs into a scenario like this, this is what I ended up doing, which is pretty close to what M_M was getting at; I broke away the full-text pieces and placed them behind branches:

DECLARE @TableBfullSearch TABLE (TableAId int) IF(@TableBSearchInfo IS NOT NULL) INSERT INTO @TableBfullSearch SELECT TableAId FROM TableB WHERE ...(fulltext search)... DECLARE @TableCfullSearch TABLE (TableAId int) IF(@TableCSearchInfo IS NOT NULL) INSERT INTO @TableCfullSearch SELECT TableAId FROM TableC WHERE ...(fulltext search)... --main query with this addition in the where clause SELECT ... FROM TableA WHERE ... AND (@TableBSearchInfo IS NULL OR TableAId IN (SELECT TableAId FROM @TableBfullSearch)) AND (@TableCSearchInfo IS NULL OR TableAId IN (SELECT TableAId FROM @TableCfullSearch))

I think that's probably about as good as it'll get without some sort of dynamic query