Replace Cursor for better performance?

[fredong]

Hi,
I have a cursor that is causing performance and I want to replace it for better performance what would be a best way to do it. Thanks.



set @SID = 0
Declare 4D_curs Cursor LOCAL FAST_FORWARD
for Select distinct SID
from @4Ept

Open 4D_curs
Fetch Next from 4D_curs into @SID
WHILE @@FETCH_STATUS = 0 BEGIN


set @VID = 0
Select top 1 @VID = VID
from dbo.Vts (nolock)
where SID = @SID
order by VL desc, CDL desc
set @VID = IsNull(@VID,0)

Insert Into @4Sp
Select @SPD, @VID


FETCH NEXT FROM 4D_curs INTO @SID
END
CLOSE 4D_curs
DEALLOCATE 4D_curs

 

[bborissov]

SELECT @SID = MIN(SID) from @4Ept
WHILE @SID IS NOT NULL
      BEGIN
         set @VID = 0
         Select top 1 @VID = IsNull(VID,0)
         from dbo.Vts (nolock)
         where SID = @SID
         order by VL desc, CDL desc

         Insert Into @4Sp
         Select @SPD, @VID
         SELECT @SID = MIN(SID) from @4Ept WHERE Sid > @SID
      END

not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[fredong]

Thanks again Borissov.

 

[fredong]

is there supposed to have a Break for this loop? Please advise. Thanks.

 

[bborissov]

Nope,
When there is no other MIN() that is bigger that current @SID the @SID will be NULL and the cycle ends. But if you want to terminate the loop somewhere you must add BREAK of some condition.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP