please optimize this stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

please optimize this stored procedure

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-12-13 : 05:41:59

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'pr_cbs_upd_rlbk'
AND type = 'P')
DROP PROCEDURE pr_cbs_upd_rlbk
GO
/*******************************************************************
* PROCEDURE: pr_cbs_upd_rlbk
* CREATED: Dasu Gairaboni 22-08-2004
* LAST UPDATED ON: 22-08-2004
*
* Variables in:
*
*
* pintJobId - Job identifier
*
* Variables out:
pintStat - Stores stored procedures return value statement

*
*
* Description: For the given jobid this stored procedure rollbacks all the updates
have done on request response table .

* DATE AUTHOR DESCRIPTION
*-------------------------------------------------------------------
*{modification description}
*
*******************************************************************/

CREATE PROCEDURE pr_cbs_upd_rlbk @pintJobId INT,@pintStat INT OUTPUT
AS

/*
VARIABLE DESCRIPTION

VARIABLE NAME TYPE OF VARIABLE DESCRIPTION

@intUpdErr INT Update error code

@intFail INT Stores failure status

@intSuccess INT Stores success status

@strErrMsg VARCHAR(255) Error information

@strErrCD VARCHAR(30) Error code

@strMdulNm VARCHAR(30) Name of the module

@intJobid INT Stores jobid

@intUpdJbobid INT Stores update jobid

@strActNo VARCHAR(30) Stores account number

@intAStatCd INT Stores audit table's status code

@intDStatCd INT Stores request response tabel's status code

@intRqstCd INT Stores request code

@dtValDt DATETIME Stores request valuation date

@intErrRsnId INT Stores error reason id
*/

--Declaration of local variables

DECLARE
@intUpdErr INT,

@intFail INT ,

@intSuccess INT,

@strErrMsg VARCHAR(255),

@strErrCD VARCHAR(30),

@strMdulNm VARCHAR(30),
@intJobid INT,
@intUpdJbobid INT,
@strActNo VARCHAR(30),
@intAStatCd INT,
@intDStatCd INT,
@intRqstCd INT,
@dtValDt DATETIME,
@intErrRsnId INT

SET @intFail =DBO.FN_CBS_FAIL() --Generic constant for failure
SET @intSuccess =DBO.FN_CBS_SUCCESS() --Generic constant for success
SET @strMdulNm ='pr_cbs_upd_rlbk' --Stores module name

-- Validation for jobid

IF NOT EXISTS (SELECT upd_job_id FROM T_CBS_RQST_DTLS WHERE upd_job_id=@pintJobId)

RETURN

/* Update the jobstatus to invalid when the requested feed is trying to rollback.
For example The outbound feed epi.req.txnpos.dts will populate the t_cbs_rqst_dtls
table.After populating the records into t_cbs_rqst_dtls table if the feed fails to
execute,the jobstatus has to rollback to previos status.But we do not have previos
status in audit table so we make this status code as invalid.This piece of code will
execute when job_id is equal to upd_job_id*/
SELECT @intJobid=job_id,@intUpdJbobid=upd_job_id FROM T_CBS_RQST_DTLS WHERE job_id=@pintJobId
IF (@intJobid=@intUpdJbobid)
BEGIN

UPDATE t_cbs_rqst_dtls
SET stat_cd=DBO.FN_CBS_INVALIDATED(),upd_job_id=@pintJobId
WHERE upd_job_id=@pintJobId
AND job_id=upd_job_id
IF (@@ERROR=0)
--Logs information message about successful rollback
BEGIN
SET @pintStat=@intSuccess
SET @strErrMsg ='Status code updated to '+CAST (DBO.FN_CBS_INVALIDATED() AS VARCHAR(30))
SET @strErrCD=DBO.FN_CBS_INFO_UPD_RLBK()
exec pr_cbs_log_error @pintJobId,@strMdulNm,@strErrCD,@strErrMsg
RETURN
END
ELSE
-- Error handling for update statement
BEGIN
SET @pintStat=@intFail

SET @strErrMsg ='SQL ERROR NO IS '+ CAST (@intUpdErr AS VARCHAR(30))+'occured while
updating status code to invalidate'
SET @strErrCD=DBO.FN_CBS_UPD_ERR()

exec pr_cbs_log_error @pintJobId,@strMdulNm,@strErrCD,@strErrMsg

RETURN
END

END

/* Update the jobstatus to its previous status when response feed is trying to rollback.
For example The inbound feed epi.in.posapos.dts will update the t_cbs_rqst_dtls table.
After updating the records into t_cbs_rqst_dtls table if feed fails to execute,
jobstatus has to rollback to previos status by using audit table.This piece of code
will execute if job_id not equals to upd_job_id */

DECLARE curRqstDtls CURSOR LOCAL FOR
select acct_no,val_dt,rqst_cd from t_cbs_rqst_Dtls where upd_job_id=@pintJobId

-- Open cursor named curRqstDtls
OPEN curRqstDtls

-- Search for first record set in curRqstDtls

FETCH NEXT FROM curRqstDtls
INTO @strActNo,@dtValDt,@intRqstCd
-- Looping through all the records
WHILE @@FETCH_STATUS=0
BEGIN
-- update stat_cd and upd_job_id and err_rsn_id to its previous status

select @intDStatCd=stat_cd,@intJobid=job_id,@intErrRsnId=err_rsn_id from t_cbs_rqst_dtls_adt where acct_no=@strActNo
and rqst_cd=@intRqstCd
and sys_ts=(select max(sys_ts) from t_cbs_rqst_dtls_adt
where acct_no=@strActNo
and rqst_cd=@intRqstCd
and job_id<>@pintJobId)

update t_cbs_rqst_dtls set stat_cd=@intDStatCd,
upd_job_id=@intJobid,
err_rsn_id=@intErrRsnId

where upd_job_id=@pintJobId
and rqst_cd=@intRqstCd
and acct_no=@strActNo
and val_dt =@dtValDt
SET @intUpdErr=@@ERROR
IF(@@ERROR<>0)
BEGIN
SET @pintStat=@intFail

SET @strErrMsg ='SQL ERROR NO IS '+ CAST (@intUpdErr AS VARCHAR(30))+'occured while
updating status code '
SET @strErrCD=DBO.FN_CBS_UPD_ERR()

exec pr_cbs_log_error @pintJobId,@strMdulNm,@strErrCD,@strErrMsg

RETURN
END

-- Fetch next record from curser
FETCH NEXT FROM curRqstDtls
INTO @strActNo,@dtValDt,@intRqstCd
end
CLOSE curRqstDtls
-- Close curRqstDtls cursor
DEALLOCATE curRqstDtls
IF (@intUpdErr=0)

BEGIN
SET @pintStat=@intSuccess
SET @strErrMsg ='Successfully updating stat_cd to its previous status '
SET @strErrCD=DBO.FN_CBS_INFO_UPD_RLBK()
exec pr_cbs_log_error @pintJobId,@strMdulNm,@strErrCD,@strErrMsg
RETURN
END

Some times this stored procedure taking 44 mnts for 20000 records
somet times it is processing with in 3 mnts.
why this difference suggest me proper solution with out cursor.
please.
dasu.g

Kristen
Test

22859 Posts

Posted - 2004-12-13 : 05:54:26

Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43619

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources