Procedure Optimization in Sybase

[singhc]

I need help in optimizing the following deletion script.

CREATE PROCEDURE MSClean
AS

DECLARE @sqlstatus INTEGER,
@exec_return INTEGER,
@appl_key_id INTEGER,
@count INTEGER,
@time datetime,
@appl_name VARCHAR(64)

DECLARE appl_cur CURSOR FOR
SELECT @appl_key_id = dsi_key_id,
@appl_name = ApplName
FROM NF_appl
WHERE ApplName LIKE "%:%:%"

OPEN appl_cur

IF (@@error <> 0)
BEGIN
DEALLOCATE CURSOR appl_cur

/* Log error */
EXEC ppReportPackProcLog &quot;MSCleanp&quot;, &quot;Error opening appl cursor&quot;, 1

RETURN
END

SELECT @count = 99999999

SELECT @sqlstatus = 0

WHILE (@sqlstatus = 0)
BEGIN
FETCH appl_cur
INTO @appl_key_id, @appl_name

IF (@@error <> 0)
BEGIN
CLOSE appl_cur
DEALLOCATE CURSOR appl_cur

/* Log error */
EXEC ppReportPackProcLog &quot;MSCleanUp&quot;, &quot;Error fetching appl cursor&quot;, 1

END

SELECT @sqlstatus = @@sqlstatus

IF (@sqlstatus <> 0)
BREAK

SELECT @count=@count+1

IF (@count > 10000)
BEGIN
SELECT @count=0
select @time= getdate()
print 'START Updating statistics for IFDP at %1!', @time

update statistics IFDP

select @time= getdate()
print ' Updating statistics for IFSCAtemp at %1!', @time

update statistics IFCSAtemp

update statistics IFclientserverappl

update statistics IFapplcust

update statistics F_appl

select @time= getdate()
print 'Done Updating statistics at %1!', @time

END

-- Create temporary table to hold keys to remove from K_NetFlowIFDP
CREATE TABLE #IFDPlist (
keyid INTEGER NOT NULL
)

/* Capture keyids */

INSERT #IFDPlist (keyid)
SELECT keyid = dsi_key_id
FROM IFDP
WHERE ApplID = @appl_key_id

DELETE FROM IFDP
WHERE dsi_key_id IN (SELECT keyid FROM #IFDPlist )

DUMP TRANSACTION dpipe_db WITH TRUNCATE_ONLY

DROP TABLE #IFDPlist