Stored Procedure Taking very Long to execute

Question

IF(@PAGEFROM='SALESINVOICE')
BEGIN
INSERT INTO TBL_ACCOUNTTRANSACTION(
ReferenceNo, Date, NepaliDate, LedgerID, BankName, Debit, Credit, Balance, PageFrom, UCDRAmount, UCCRAmount, GroupID, SubGroup, 
CategoryID, AccountType, SubType, Currency, Subledger, Agent, Narration, Remarks, PartyNo, PartyDate, CreatedBy,LStatus
)
SELECT INVOICENO,DATE,NEPALIDATE,CASE WHEN TYPE='1' THEN '1' ELSE VENDOR END,'',
case when TotalAmt='0.00' then TermAmt else TotalAmt end,'0.00','0.00','SB','0.00','0.00',lai.groupid,
SubGroupID,lai.CategoryID,lai.AccountType,MAS.AccountSubType,'0.00',
CASE WHEN SI.SubLedger IS NULL THEN '' ELSE SI.SubLedger END,
CASE WHEN AgentID IS NULL THEN '' ELSE AGENTID END,'',Remarks,'','',SI.CREATEDBY,'U'
FROM TBL_SALESINVOICE SI
LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID= CASE WHEN TYPE='1' THEN 1 ELSE SI.VENDOR END
LEFT JOIN TBL_ACCOUNTGROUP AG ON AG.ACCOUNTGROUPID=LAI.GROUPID
LEFT JOIN TBL_MASTERACCOUNTSUBTYPE AS MAS ON MAS.ACCOUNTSUBTYPEID=AG.ACCOUNTSUBTYPEID 
LEFT JOIN TBL_CATEGORY CG ON CG.CATEGORYID=LAI.CATEGORYID
WHERE INVOICENO=@NO

UNION ALL
SELECT SIP.INVOICENO,DATE,NEPALIDATE,SALESLEDGER,'','0.00',SUM(BasicAmount),'0.00','SB','0.00','0.00',lai.groupid,
SubGroupID,lai.CategoryID,lai.AccountType,MAS.AccountSubType,'0.00','','','','','','',SIP.CREATEDBY,'L'
FROM TBL_SALESINVOICEPRODUCT SIP
LEFT JOIN TBL_SALESINVOICE SI ON SI.INVOICENO=SIP.INVOICENO
LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=SIP.SALESLEDGER
LEFT JOIN TBL_ACCOUNTGROUP AG ON AG.ACCOUNTGROUPID=LAI.GROUPID
LEFT JOIN TBL_MASTERACCOUNTSUBTYPE AS MAS ON MAS.ACCOUNTSUBTYPEID=AG.ACCOUNTSUBTYPEID 
LEFT JOIN TBL_CATEGORY CG ON CG.CATEGORYID=LAI.CATEGORYID
WHERE SIP.INVOICENO=@NO
GROUP BY SIP.INVOICENO,DATE,NEPALIDATE,SALESLEDGER,lai.groupid,
SubGroupID,lai.CategoryID,lai.AccountType,MAS.AccountSubType,SIP.CREATEDBY 

UNION ALL
SELECT
SD.CHALLANNO,DATE,NEPALIDATE,SB.LEDGERID,'',
SUM(abs (CASE WHEN (Amount<0) THEN AMOUNT ELSE '0.00' END)),  
SUM(abs (CASE WHEN (Amount>0) THEN AMOUNT ELSE '0.00' END)),
'0.00','SB','0.00','0.00',lai.groupid,SubGroupID,lai.CategoryID,
lai.AccountType,MAS.AccountSubType,'0.00','','','','','','',SD.CREATEDBY,'L'
FROM TBL_SALESBILLING SB   
LEFT JOIN TBL_SALESDISCOUNT SD ON SB.DESCRIPTION=SD.DESCRIPTION    
LEFT JOIN TBL_SALESINVOICE SI ON SI.INVOICENO=SD.CHALLANNO
LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=SB.LEDGERID
LEFT JOIN TBL_ACCOUNTGROUP AG ON AG.ACCOUNTGROUPID=LAI.GROUPID
LEFT JOIN TBL_MASTERACCOUNTSUBTYPE AS MAS ON MAS.ACCOUNTSUBTYPEID=AG.ACCOUNTSUBTYPEID 
LEFT JOIN TBL_CATEGORY CG ON CG.CATEGORYID=LAI.CATEGORYID
WHERE PageFrom ='SALESINVOICE' AND SD.CHALLANNO=@NO
GROUP BY 
SD.CHALLANNO,DATE,NEPALIDATE,SB.LEDGERID,lai.groupid,SubGroupID,lai.CategoryID,lai.AccountType,MAS.AccountSubType,SD.CREATEDBY

UNION ALL
SELECT
INVOICENO,DATE,NEPALIDATE,SB.LEDGERID,'',
(abs (CASE WHEN (Amount<0) THEN AMOUNT ELSE '0.00' END)),  
(abs (CASE WHEN (Amount>0) THEN AMOUNT ELSE '0.00' END)),
'0.00','SB','0.00','0.00',lai.groupid,SubGroupID,lai.CategoryID,
lai.AccountType,MAS.AccountSubType,'0.00','','','','','','',SBT.CREATEDBY,'L'
FROM TBL_SALESBILLING SB   
LEFT JOIN TBL_SALESBILLTERM SBT ON SB.DESCRIPTION=SBT.DESCRIPTION    
LEFT JOIN TBL_SALESINVOICE SI ON SI.INVOICENO=SBT.CHALLANNO
LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=SB.LEDGERID
LEFT JOIN TBL_ACCOUNTGROUP AG ON AG.ACCOUNTGROUPID=LAI.GROUPID
LEFT JOIN TBL_MASTERACCOUNTSUBTYPE AS MAS ON MAS.ACCOUNTSUBTYPEID=AG.ACCOUNTSUBTYPEID 
LEFT JOIN TBL_CATEGORY CG ON CG.CATEGORYID=LAI.CATEGORYID
WHERE PageFrom ='SALESINVOICE' AND AMOUNT<>'0.00' AND INVOICENO=@NO

    DECLARE INVOICENO 
    CURSOR local FOR Select DISTINCT AT.LEDGERID,ACCOUNTNAME FROM TBL_ACCOUNTTRANSACTION AT 
    LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=AT.LEDGERID
    WHERE PAGEFROM='SB' AND REFERENCENO=@NO
    UNION ALL
    Select DISTINCT AT.ELEDGERID,ACCOUNTNAME FROM TBL_ACCOUNTTRANSACTION AT 
    LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=AT.ELEDGERID
    WHERE PAGEFROM='SB' AND AT.LEDGERID<>ELEDGERID AND ELEDGERID<>'' AND REFERENCENO=@NO 

    OPEN INVOICENO
    FETCH FROM INVOICENO INTO @LEDGERID,@ACCOUNTNAME
    WHILE (@@FETCH_STATUS = 0) 
    BEGIN 
        Exec RunningTotal @ACCOUNTNAME       
        FETCH NEXT FROM INVOICENO INTO @LEDGERID,@ACCOUNTNAME
    END
END

I recently joined a company and was given a task to optimize a stored procedure. Above is a part of stored procedure. The problem is there are 4 similar queries in this stored procedure: purchase invoice, purchase return, sales invoice and sales return and it takes very long time to execute may be cause of the data because there is pretty large data in tables but I also feel it has something to do with joins present and used. If you require full stored procedure which is about 400 lines of code do let me know.

This is the error message I get
SQL ERROR

I tried using clustered index in tables looking up from internet but I do not think it made any difference

Answer 1

You're executing a procedure (EXEC RunningTotal) for every row in the cursor INVOICENO, as noted in the comments. As a general principle, cursors should be avoided wherever it's possible to express the same logic without them, particularly when they're operating on a large number of rows.

This post summarizes some of the reasons cursors are problematic.

I would start by investigating whether or not you could refactor this UNION to incorporate the logic in the procedure RunningTotal, as a single table expression (i.e. without the cursor).

Select DISTINCT AT.LEDGERID,ACCOUNTNAME FROM TBL_ACCOUNTTRANSACTION AT 
LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=AT.LEDGERID
WHERE PAGEFROM='SB' AND REFERENCENO=@NO
UNION ALL
Select DISTINCT AT.ELEDGERID,ACCOUNTNAME FROM TBL_ACCOUNTTRANSACTION AT 
LEFT JOIN TBL_LEDGERACCINFO LAI ON LAI.LEDGERID=AT.ELEDGERID
WHERE PAGEFROM='SB' AND AT.LEDGERID<>ELEDGERID AND ELEDGERID<>'' AND REFERENCENO=@NO