CURSOR and SUBQUERY

[MissyEd]

When I remove the commented line, this works in about 3 secs. Adding the commented line (subquery) causes it to hang. When I run just the SELECT statement with the commented line in, it also takes about 3 secs.

So.. in summary, my code works as a select statement but breaks as a cursor.

Any ideas ?

DECLARE @FileId INT

DECLARE @ProcMsg VARCHAR(2000)
DECLARE @ErrCount INT
DECLARE @NumRec INT

SET @NumRec = 0
SET @ErrCount = 0


DECLARE LoopCur CURSOR READ_ONLY FOR
SELECT tf.FileId
FROM Table1 tf WITH (NOLOCK)
INNER JOIN Table2 tat WITH (NOLOCK) ON tf.AppointmentId = tat.[Id]
INNER JOIN Table3 tad WITH (NOLOCK) ON tat.AppointmentId = tad.[Id]
WHERE tf.ClientId = 199
AND tf.FileReceivedDate IS NULL
AND tf.CancelledDate IS NULL
AND tat.Status NOT IN ('a', 'c')
--AND NOT EXISTS (SELECT tac.FileId FROM Table4 tac WITH (NOLOCK) WHERE tac.TypeId = 234 AND tac.FileId = tf.FileId)
ORDER BY tad.[Date] + 6

OPEN LoopCur


FETCH NEXT FROM LoopCur INTO @FileId

WHILE @@FETCH_STATUS = 0
BEGIN
--EXEC SP HERE
SET @NumRec = @NumRec + 1
FETCH NEXT FROM LoopCur INTO @FileId

END

CLOSE LoopCur
DEALLOCATE LoopCur

PRINT @NumRec


Life is a blast when you have a semi-automatic..

 

[SQLSister]

Frankly, you should avoid cursors. It might be better to rewrite this to do whatever you are planning to do with the cursor in a set-based fashion. Cursors are very inefficeint and may take minutes or even hours to do what can be done ina set-based way in milliseconds (or minutes for the ones whoich take hours).

So what is it you are intending to do inseide the cursor?

Questions about posting. See faq183-874

 

[MissyEd]

Execute a SP - I dont think you can do this SET based... I do avoid cursors where possible but dont really have a choice in this case - happy to be proved wrong though!

Life is a blast when you have a semi-automatic..

 

[SQLSister]

What is in the sp? It is possible the code would be better written in this sp as a set-based statment than executing an sp designed to act on only one record at a time multiple times through a cursor. Code reuse is all well and good, but not when it affects performance as badly as using a cursor would do. (Unless you are sure you will only ever be executuing a few rows, then performance might be acceptable if still slower than a set-based command would be.)

Also not exists is a slow way to do things. Added to a cursor you may be timing out. Use a left join instead. And then add a where clause to specify when the id field in the second table is null.

Questions about posting. See faq183-874

 

[MissyEd]

Sorry, should have made myself clear, Im not looking for an alternative solution to this problem or coding tips, I simply want to understand why it is happening.

The SP can't be re-written and I can use a LEFT OUTER JOIN (in fact I have done something similar), however, the question is WHY the cursor gets stuck executing the EXISTS statement (a subquery i.e NOT IN also fails).

Ive noticed it fails when it hits the first FETCH. Im curious why it doesnt time out when I run the SELECT statement on its own. Any insight to this would be helpful.

Life is a blast when you have a semi-automatic..