将Stk_Out表中当前月的内容更新Stk_Out_Sum,其中Stk_Out有7万条数据,Stk_Out_Sum有100万条数据,更新时间是5分钟,请各位指点该如何更新,谢谢!
update stk_out_sum
set type_seq=a.type_seq,
require_no=a.require_no,
cause=a.cause,
memo=a.memo,
location=a.location ,
created_date=a.created_date,
modified_date=a.modified_date,
order_no=a.order_no,
ref_no=a.ref_no,
to_company_code=a.to_company_code,
to_stk_div_code=a.to_div_code,
cut_no_fp=a.cut_no_fp,
fq_status=a.fq_status,
grp_code=a.grp_code
from stk_out a,
stk_out_sum b
where a.out_no=b.out_no and
a.created_date >= (select d.cur_month
from stk_system_config d
where b.company_code=d.company_code and
b.stk_div_code=d.division_code
)and
a.created_date < (select dateadd(month,1,d.cur_month)
from stk_system_config d
where b.company_code=d.company_code and
b.stk_div_code=d.division_code
)
update stk_out_sum
set type_seq=a.type_seq,
require_no=a.require_no,
cause=a.cause,
memo=a.memo,
location=a.location ,
created_date=a.created_date,
modified_date=a.modified_date,
order_no=a.order_no,
ref_no=a.ref_no,
to_company_code=a.to_company_code,
to_stk_div_code=a.to_div_code,
cut_no_fp=a.cut_no_fp,
fq_status=a.fq_status,
grp_code=a.grp_code
from stk_out a,
stk_out_sum b
where a.out_no=b.out_no and
a.created_date >= (select d.cur_month
from stk_system_config d
where b.company_code=d.company_code and
b.stk_div_code=d.division_code
)and
a.created_date < (select dateadd(month,1,d.cur_month)
from stk_system_config d
where b.company_code=d.company_code and
b.stk_div_code=d.division_code
)
set
type_seq = a.type_seq,
require_no = a.require_no,
cause = a.cause,
memo = a.memo,
location = a.location ,
created_date = a.created_date,
modified_date = a.modified_date,
order_no = a.order_no,
ref_no = a.ref_no,
to_company_code = a.to_company_code,
to_stk_div_code = a.to_div_code,
cut_no_fp = a.cut_no_fp,
fq_status = a.fq_status,
grp_code = a.grp_code
from
stk_out a,
stk_out_sum b,
stk_system_config c
where
a.out_no=b.out_no
and
b.company_code=c.company_code
and
b.stk_div_code=c.division_code
and
a.created_date>=c.cur_month
and
a.created_date<dateadd(month,1,c.cur_month)
set type_seq=a.type_seq,
require_no=a.require_no,
cause=a.cause,
memo=a.memo,
location=a.location ,
created_date=a.created_date,
modified_date=a.modified_date,
order_no=a.order_no,
ref_no=a.ref_no,
to_company_code=a.to_company_code,
to_stk_div_code=a.to_div_code,
cut_no_fp=a.cut_no_fp,
fq_status=a.fq_status,
grp_code=a.grp_code
from stk_out a
inner join
stk_out_sum b
on a.out_no=b.out_no
left join
stk_system_config d
on
b.company_code=d.company_code and
b.stk_div_code=d.division_code
where
a.created_date >= d.cur_month and
a.created_date < dateadd(month,1,d.cur_month)注意需要索引:
stk_out(out_no,created_date)
stk_out_sum(out_no)
stk_out_sum(company_code,stk_div_code)
stk_system_config (company_code,division_code)
你的方法我已经试过,速度还是没有很大的提高,谢谢你的帮助.
http://community.csdn.net/Expert/topic/5128/5128216.xml?temp=.7214319
http://community.csdn.net/Expert/topic/5128/5128233.xml?temp=.140072
http://community.csdn.net/Expert/topic/5128/5128222.xml?temp=.5970423
好像是最优化了,
注意索引。
可以考虑建立索引stk_system_config (company_code,division_code,cur_month )
我也建立了索引
用这样方法
update table1
set 列b=select 列b from where table2 where c=table1.c)
where 在此定义table1的条件
a.created_date >= (select d.cur_month
from stk_system_config d
where b.company_code=d.company_code and
b.stk_div_code=d.division_code
)and
a.created_date < (select dateadd(month,1,d.cur_month)
from stk_system_config d
where b.company_code=d.company_code and
b.stk_div_code=d.division_code
)
看看有没有办法进行一些优化,比如有些数据可以预先处理比如先将这两项日期先处理出来,d表貌似和a表没有什么大的关系,如果能不掺乎到a表的update 应该速度会有些提高
SET type_seq = a.type_seq,
require_no = a.require_no,
cause = a.cause,
memo = a.memo,
location = a.location ,
created_date = a.created_date,
modified_date = a.modified_date,
order_no = a.order_no,
ref_no = a.ref_no,
to_company_code = a.to_company_code,
to_stk_div_code = a.to_div_code,
cut_no_fp = a.cut_no_fp,
fq_status = a.fq_status,
grp_code = a.grp_code
FROM stk_out a
INNER JOIN stk_out_sum b
ON a.out_no = b.out_no
INNER JOIN stk_system_config d
ON b.company_code = d.company_code
AND b.stk_div_code = d.division_code
AND a.created_date BETWEEN d.cur_month AND DATEADD(MONTH,1,d.cur_month)