how to optimize this querry - SQL Server Forums

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)

how to optimize this querry

Author

Topic

dasu
Posting Yak Master

104 Posts

Posted - 2004-12-05 : 23:51:32

update t_cbs_rqst_dtls
set stat_cd =(select stat_cd from t_cbs_rqst_dtls_adt
where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn()
and sys_ts= ( select max (sys_ts) from t_cbs_rqst_dtls_adt where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn() and job_id<>@pintJobId)),
upd_job_id=(select job_id from t_cbs_rqst_dtls_adt where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn()
and sys_ts=( select max(sys_ts) from t_cbs_rqst_dtls_adt where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn() and job_id<>@pintJobId)),
err_rsn_id=(select err_rsn_id from t_cbs_rqst_dtls_adt where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn()
and sys_ts=( select max(sys_ts) from t_cbs_rqst_dtls_adt where acct_no=@strActNo

regards
dasu.g

RM
Yak Posting Veteran

65 Posts

Posted - 2004-12-13 : 09:59:25

Giving it a try...

What I could presume here is that you have the same select query in all the subqueries.

I think that your subqueries return just a single row.

declare @sys_ts int (replace your datatype)

select @sys_ts = max(sys_ts)
from t_cbs_rqst_dtls_adt
where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn()
and job_id<>@pintJobId

update t_cbs_rqst_dtls
Set stat_cd = B.stat_cd
, upd_job_id = B.upd_job_id
, err_rsn_id = B.err_rsn_id
From (select stat_cd, upd_job_id, err_rsn_id
from t_cbs_rqst_dtls_adt
where acct_no=@strActNo
and rqst_cd=dbo.fn_cbs_pstn()
and sys_ts=@sys_ts) B

not sure if it will work, try it out on a BACKUP COPY of the table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources