Search code examples
sqlselectsql-server-2014

SELECT hangs when using a variable

SQL Server 2014 (v13.0.4001.0) - this sample script hangs:

DECLARE @from int = 0
DECLARE @to int = 1000

select  
    *
from 
    TaskNote dtn 
join 
    Participants tp on dtn.Task_ID = tp.TaskId
where 
    dtn.TaskNote_ID between @from and @to

But if I change variables to constants - it is all OK.

Like this: 

where 
    dtn.DocTaskNote_ID between 0 and 1000

Also, if I remove the join, all is ok.

Can't figure out where the problem is

Solution

  • A possible cause for the problem you mention, in case your query lies within a stored procedure, is parameter sniffing. SQL Server compiles the query for the first time using the initial values of the parameters. In subsequent calls to the procedure the engine uses the cached execution plan which is probably not optimal for the current variable values.

    One workaround this problem is to use OPTION (RECOMPILE):

    select *
from TaskNote dtn 
join Participants tp on dtn.Task_ID = tp.TaskId
where dtn.TaskNote_ID between @from and @to
option (recompile)

    This way the query is being compiled every time the procedure is executed using the current parameters values.

    Further reading: