SQL Server - Comparing to NULL very slow

问题:

I want to speed up the following query

There are two conditions in the WHERE clause (see below query for reference)

Currently, it takes about 60 seconds. However, if I remove the first condition in the where clause (@Query is NULL) then it returns almost immediately.

Any thoughts on how I can speed up? About 700k rows in the table and this will only grow.

(Note: The query shown below is stripped down to it's bare essence and I'm using hardcoded values strictly to simplify the query so that the focus is drawn toward the portion outlined above)

declare @Query nvarchar(255)
select @Query = 'oceans'

select
    * 
from

(select 
  row_number() over( order by b.BookTitle) as RowNumber, 
  b.*
from
  Books b (nolock)
where
 -- If I remove this first condition "@Query is NULL", then it returns almost immediately
 -- Otherwise if I keep this here, it takes around 1 minute
 -- Yes, I have full-text index on BookTitle, as well as a regular index.
  (@Query is NULL) or (contains(b.BookTitle, @Query))
) as t1

where t1.RowNumber between 40 and 60

回答1:

Can you split this into two queries? or often causes a problem for optimizers:

if @Query is null
begin
    select * 
    from (select row_number() over( order by b.BookTitle) as RowNumber, b.*
          from Books b (nolock)
          where @Query is NULL
         ) as t1
    where t1.RowNumber between 40 and 60;
end
else
begin
    select * 
    from (select row_number() over( order by b.BookTitle) as RowNumber, b.*
          from Books b (nolock)
          where contains(b.BookTitle, @Query)
         ) as t1
    where t1.RowNumber between 40 and 60;
end

回答2:

In case where @query is a parameter of a stored procedure, then the delay could be due to Parameter sniffing:

When a stored procedure is compiled or recompiled, the parameter values passed for that invocation are "sniffed" and used for cardinality estimation. The net effect is that the plan is optimized as if those specific parameter values were used as literals in the query.

The work-around used in this case is to declare a dummy local variable inside the stored procedure and assign this variable the contents of the parameter being sniffed, e.g.

CREATE PROCEDURE [dbo].[usp_MySproc](@Query nvarchar(255)
AS
BEGIN

    -- Declare dummy variable   
    DECLARE @localQuery nvarchar(255)

    -- Disable parameter sniffing       
    SET @localQuery = @Query

    -- etc ...
END

回答3:

Did you compare the execution plans with and without (@Query is NULL) ?

I would use this procedure only for searching for a specific book title and remove (@Query is NULL). When you want all book titles within a Row-Number-Range you can also use a view. When you execute a stored procedure and there is no execution plan in cache SQL Server will genereate one. Maybe the first caller uses @Query = 'ocean'. Every other call regardles of the parameter-values will use the same plan. When you call the proc with @Query = NULL SQL Server must read 700.000 Rows. But the execution plan and memory grants are based on the query for 'ocean'. So it will spill out to tempDb because there is not enough memory and the used Operators in the execution plan may not be optimal.

回答4:

Here's one option that might speed it up using coalesce:

...
where contains(b.BookTitle, coalesce(@Query,b.BookTitle))
...