First FETCH NEXT FROM cursor taking forever

Question

Yes it needs to be a cursor

The query for the cursor is very simple - runs in a fraction of a second.

But the query returns over 300,000 rows

The first call to

FETCH NEXT FROM cursor

is taking forever - like 10 minutes.

What is going on? What can I do to fix this?

Some times it runs very fast.

If I have not run the TSQL for a while then some times it is very fast.

set nocount off 
DECLARE @randCountNew Int;

select @randCountNew = COUNT(*)
from [docSVsys] with (nolock) 
where [docSVsys].[visibility] in (0)
  and [docSVsys].[rand] = 1 ;

select @randCountNew;

 DECLARE @sIDprecict Int; 
 DECLARE @randCountThis Int;
 DECLARE @valueIDthis SmallInt;
 DECLARE @lockIDthis TinyInt;

select 'start CRREATE predict_cursor '

DECLARE predict_cursor CURSOR FOR 

SELECT [predict].[sID], [docSVenum1pred].[randCount], [docFieldLock].[lockID], [docSVenum1].[valueID] 
FROM [docSVsys] as [predict] with (nolock) 
left join [docSVsys] as [sample] with (nolock) on [sample].[docHash] = [predict].[docHash] 
                                               and [sample].[rand] = 1
left join [docFieldLock] with (nolock) on [docFieldLock].[sID] = [predict].[sID] 
                                       and [docFieldLock].[fieldID] = 61 
                                       and [docFieldLock].[lockID] >= 3
left join [docSVenum1pred] with (nolock) on [docSVenum1pred].[sID] = [predict].[sID] 
                                         and [docSVenum1pred].[enumID] = 61
left join [docSVenum1] with (nolock) on [docSVenum1].[sID] = [sample].[sID] 
                                     and [docSVenum1].[enumID] = 61 
WHERE 
    ([predict].[RAND] is null or [predict].[RAND] = 0)
    and [predict].[textUniqueWordCount] > 10 
    and [predict].[visibility] in (0)
    and [sample].[docHash] is null 
    and [docFieldLock].[sID] is null 
    and ([docSVenum1pred].[randCount] is null or [docSVenum1pred].[randCount] <> @randCountNew)
   --and [predict].[sID] = 379045
ORDER BY [predict].[sID];

OPEN predict_cursor

Select 'FETCH NEXT FROM predict_cursor'

FETCH NEXT FROM predict_cursor INTO @sIDprecict, @randCountThis, @lockIDthis, @valueIDthis

Select 'starting cursor'

CLOSE predict_cursor;
DEALLOCATE predict_cursor;

Select 'done'

Every column is indexed an those joins are on PK, FK

Answer 1

Make sure you always close and deallocate your cursors - or avoid their use if possible.

CLOSE predict_cursor;
DEALLOCATE predict_cursor;