Sybase stored procedure taking too long

Question

The following stored proc is running 4 hrs. Is there anyway I can optimize it to bring down execution time under 10 mins?

Table #TbTemp has 150k rows. And the cursor is looping 150k times.

Since I'm dealing with Temp tables, I will have full control on creating any indices -etc.

Since cursors should be avoided, any alternate solution?

declare tmpCur cursor
for
select LnNo,instDrftCreatnDt
from   #TbTemp
for read only

open tmpCur

while (1 = 1)
begin   --{
    fetch tmpCur into @LnNo,@Dt

    insert into #TmpLnPmt
            (RecTyp,InstNo,LnNo,TotCurChrgdFactr,PmtTyp,
            CurrChrgdFactr,CurrPmtAmt,PrevCmptdPmtAmt,NetCmptdPmtAmt)
    select  '01', @InstNo, @LnNo, @TotCurChrgdFactr, pc.PmtTyp,
            0, 0, pc.cmptdPmtAmt, 0-pc.cmptdPmtAmt
    from    Ln l, Pmt pt, PmtCmpnt pc
    where   l.LnNo = @LnNo
    and     pt.mbsLoanPID = l.identifier
    and     pt.instDrftCreatnDt = @Dt
    and     pc.paymentComponentsPaymentPID = pt.identifier
    and     pc.PmtTyp not in (select   PmtTyp
                    from    #TbTemp
                    where   LnNo = @LnNo)
    and     not exists (select  1 from #TmpLnPmt
                where   LnNo = @LnNo
                and     PmtTyp = pc.PmtTyp)
    end --}
close tmpCur
deallocate cursor tmpCur                            

Answer 1

Here is one way without using CURSOR

INSERT INTO #tmplnpmt 
            (rectyp, 
             instno, 
             lnno, 
             totcurchrgdfactr, 
             pmttyp, 
             currchrgdfactr, 
             currpmtamt, 
             prevcmptdpmtamt, 
             netcmptdpmtamt) 
SELECT '01', 
       @InstNo, 
       a.lnno, 
       @TotCurChrgdFactr, 
       pc.pmttyp, 
       0, 
       0, 
       pc.cmptdpmtamt, 
       0 - pc.cmptdpmtamt 
FROM   ln l 
       INNER JOIN pmt pt 
               ON pt.mbsloanpid = l.identifier 
       INNER JOIN pmtcmpnt pc 
               ON pc.paymentcomponentspaymentpid = pt.identifier 
       INNER JOIN #tbtemp a 
               ON l.lnno = a.lnno 
                  AND pt.instdrftcreatndt = a.instdrftcreatndt 
WHERE  NOT EXISTS (SELECT pmttyp 
                   FROM   #tbtemp t 
                   WHERE  t.pmttyp = pc.pmttyp) 
       AND NOT EXISTS (SELECT 1 
                       FROM   #tmplnpmt a 
                              INNER JOIN #tbtemp b 
                                      ON a.lnno = b.lnno 
                                         AND pmttyp = pc.pmttyp)