|
|
Posted by Erland Sommarskog on 10/20/05 01:07
Jimbo (jim.ferris@motorola.com) writes:
> I have a cursor here that appears to never stop running even though the
> record set that populates it is only 22 records
>
> heres the code:
>
> declare cursorfinal cursor for
> select appointmenteffdate, appointmentDuration, provideroid from @main
>
> open cursorfinal
>
> FETCH NEXT FROM cursorfinal
> INTO @aff, @duration, @poid
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> delete from @main_temp where (appointmenteffdate between @aff and
> dateadd(minute, @duration , @aff) or
> dateadd(minute,appointmentduration,appointmenteffdate ) between @aff
> and dateadd(minute, @duration , @aff))
> and provideroid = @poid
> select @poid
>
> END
>
> CLOSE cursorfinal
> DEALLOCATE cursorfinal
You need to move the FETCH into the loop:
WHILE 1 = 1
BEGIN
FETCH ...
IF @@fetch_status <> 0
BREAK
-- Do stuff
END
However, there is no reason to write a cursor at all here:
delete @main_temp
from @main_temp t
where EXISTS
(SELECT *
FROM @main m
WHERE m.provideroid = t.provideroid
AND (t.appointmenteffdate between
m.appointmenteffdate and
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)
or dateadd(minute, t.appointmentduration,
t.appointmenteffdate)
between m.appointmenteffdate AND
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)))
It's essential that you lear to operations like this in a set-based
fashion. Maybe your cursor over 22 rows runs quickly, but what if
real production data has 20000 rows? The difference beween the
cursor and a set-based statement like the above, can easily be a
factor of 1000 in such case.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Navigation:
[Reply to this message]
|