SQL Server - Comparing to NULL very slow

Question

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

Answer 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

Answer 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