- 论坛徽章:
- 0
|
请各位大侠帮忙看看,我的sybase库问题出在哪儿?,关键瓶颈是哪儿?
下面是存储过程，我调试了一下，其中打红色标记的部分，也就是
insert xu_test values("032-2s",getdate()) 和
insert xu_test values("032-3s",getdate()) 之间这一段用了50分钟，
两个表#wkt_guar 和#wkt_guar1 的记录数都在6万条左右，
但是我把这俩表 单独在该时段提取出来，然后单独执行红体标记的这一段update语句，结果10秒就出来了，我以为是tempdb的问题，但tempdb有500兆，而且我看了一下使用率很低，怪啊，难题！
create proc spcbi_chk_guar03 @dept_code char(6),@dept_code2 char(6) = null as
declare @bank_code char(6)
declare @host_id int
declare @proc_beg_time datetime
select @bank_code = option_value from cdt_system_option
where option_id = '01'
select @host_id = convert(int,host_id())
select @proc_beg_time = getdate()
INSERT cdt_proc_runtime
SELECT @host_id,@bank_code,"chkgetgu03",@proc_beg_time,null
INSERT cdt_proc_time_dtl
SELECT @host_id,@bank_code,"chkgetgu030",getdate()
declare @rows int, @err int,@err_msg varchar(255)
declare @area_code char(12)
select @area_code = t1.bank_code
from cbi_general t1,cbi_area_stat t2
where t1.bank_code = t2.area_code and
t2.dept_code = @dept_code
select @rows = @@rowcount
if @rows < 1
begin
select @err_msg = "无法读取总体信息!"
select -1,@err_msg
return -1
end
create table #wkt_guar (
business_type char(1) null,
dept_code char(6) null,
account char(1 null,
flow_no char(20) null,
currency char(2) null,
amount money null,
balance money null,
guar_amt money null,
custom_code char(20) null,
custom_name char(40) null,
guar_code char(20) null,
guar_type char(4) null,
guar_kind char(4) null,
guar_class char(1) null,
guar_no int null,
error_id1 char(1) null,
error_id2 char(1) null)
select dept_code
into #wkt_deptcode
from cbi_area_stat
where area_code = @area_code
insert #wkt_guar
select '1',t1.dept_code,t1.account,t1.flow_no,t1.currency,loan_amount,balance,0,t1.custom_code,' ',
t2.guarantee_code,t2.guarantee_type,t1.loan_mode,t1.guarantee_class,0,'0','0'
from cdt_loan_card t1,cdt_loan_guarantee t2
where t2.business_type = '1' and
t1.dept_code *= t2.dept_code and
t1.account *= t2.account and
t1.flow_no *= t2.flow_no
insert #wkt_guar
select '2',t1.dept_code,'',t1.credit_no,t1.currency,issue_sum,restissue_sum,guarantee_sum,t1.custom_code,' ',
t2.guarantee_code,t2.guarantee_type,t1.guarantee_kind,t1.guarantee_class,0,'0','0'
from cdt_letter_credit t1,cdt_loan_guarantee t2
where t2.business_type = '2' and
t1.modi_num = 0 and
t1.credit_no *= t2.flow_no and
t1.issue_sum >; t1.guarantee_sum
insert #wkt_guar
select '3',t1.dept_code,'',t1.draft_no,t1.currency,draft_sum,draft_sum,guarantee_sum,t1.custom_code,' ',
t2.guarantee_code,t2.guarantee_type,t1.guarantee_kind,t1.guarantee_class,0,'0','0'
from cdt_acceptance_draft t1,cdt_loan_guarantee t2
where t2.business_type = '3' and
t1.draft_no *= t2.flow_no and
t1.draft_sum >; t1.guarantee_sum
insert #wkt_guar
select '5',t1.dept_code,'', t1.guar_no, t1.currency,issue_sum, issue_sum - payment_sum, guarantee_sum,
t1.custom_code, ' ', t2.guarantee_code,t2.guarantee_type,t1.guarantee_kind,t1.guarantee_class, 0, '0', '0'
from cdt_bank_guarantee t1,cdt_loan_guarantee t2
where t2.business_type = '5' and
t1.modi_num = 0 and
t1.guar_no *= t2.flow_no and
t1.issue_sum >; t1.guarantee_sum
delete #wkt_guar
where dept_code not in (select dept_code from #wkt_deptcode)
INSERT cdt_proc_time_dtl
SELECT @host_id,@bank_code,"chkgetgu031",getdate()
update #wkt_guar
set error_id1 = '3'
where guar_code is null and
guar_type is not null
update #wkt_guar
set error_id1 = '1'
where error_id1 = '0' and
guar_code not in(select custom_code from cdt_custom)
update #wkt_guar
set error_id1 = '2'
where error_id1 = '0' and
guar_code not in(select custom_code from cbi_custom_stat where download_st = '1')
INSERT cdt_proc_time_dtl
SELECT @host_id,@bank_code,"chkgetgu032",getdate()
insert xu_test values("032-1s",getdate())
select business_type,dept_code,account,flow_no,num=count(*)
into #wkt_guar1
from cdt_loan_guarantee
where dept_code in (select dept_code from #wkt_deptcode)
group by business_type,dept_code,account,flow_no
insert xu_test values("032-2s",getdate())
update #wkt_guar
set guar_no = num
from #wkt_guar1
where #wkt_guar.business_type = #wkt_guar1.business_type and
#wkt_guar.dept_code = #wkt_guar1.dept_code and
#wkt_guar.account = #wkt_guar1.account and
#wkt_guar.flow_no = #wkt_guar1.flow_no
insert xu_test values("032-3s",getdate())
update #wkt_guar
set error_id2 = '1'
where substring(guar_kind,1,1) in ('1','5','6') and
guar_no >; 0
update #wkt_guar
set error_id2 = '2'
where substring(guar_kind,1,1) in ('2','3','4') and
guar_class = '0' and
guar_no != 1
update #wkt_guar
set error_id2 = '3'
where substring(guar_kind,1,1) in ('2','3','4') and
guar_class >; '0' and
guar_no < 2
insert xu_test values("032-4s",getdate())
update #wkt_guar
set custom_name = t1.custom_name_chn
from cdt_custom t1, #wkt_guar t2
where t1.custom_code = t2.custom_code
insert xu_test values("032-5s",getdate())
INSERT cdt_proc_time_dtl
SELECT @host_id,@bank_code,"chkgetgu033",getdate()
declare @transf_id int
select @transf_id = max(transfer_id) + 1
from cbi_trans_report
where area_code = @area_code
if @transf_id is null
begin
select @transf_id = 1
end
insert cbi_checkdata
select @transf_id,business_type,dept_code,account,flow_no,6005,null
from #wkt_guar
where error_id1 != '0' or error_id2 != '0'
UPDATE cdt_proc_runtime
SET end_time = getdate()
WHERE host_id = @host_id and
time_id = "chkgetgu03" and
beg_time = @proc_beg_time
if @dept_code2 is null
select count(*)
from #wkt_guar
where (error_id1 != '0' or error_id2 != '0') and
dept_code in (select dept_code from #wkt_deptcode)
else
select count(*)
from #wkt_guar
where (error_id1 != '0' or error_id2 != '0') and
dept_code = @dept_code2
go |
|
免费注册
发表于 2004-10-24 10:11
发表于 2004-10-27 08:28
