请邹建大哥帮我看看 存储过程执行太慢 怎样提高执行速度
这个存储过程用来 统计每个类型对应的计费情况
有两张表
1. plat_orderservice
字段:
service_name 名称
feetype 计费类型
feecode 费率
service_id id2
2. plat_mt
字段:
app_id id
srctermid 号码
service_id id2
feetype 计费类型
morelatetomtflag_sgip 标志
occurtime 时间
存储过程如下
CREATE PROCEDURE Plat_OrderFeeAccount --统计每个单条业务类型对应的应用计费情况
@StartDate varchar(30),
@EndDate varchar(30)
AS
set nocount on
begin transaction
select success.wapp_id, success.totalNum, fail.failNum, success.wsrctermid, success.wservice_name, success.wfeecode
from
(
select count(*) as totalNum,
mt.app_id as wapp_id,
mt.srctermid as wsrctermid,
mt.service_id as wservice_id,
od.service_name as wservice_name,
od.feecode as wfeecode
from plat_mt as mt, plat_orderservice as od
where mt.occurtime >= @StartDate and mt.occurtime <= @EndDate
and mt.feetype = '02'
and mt.morelatetomtflag_sgip = 0
and mt.service_id = od.service_id
group by mt.service_id, mt.app_id, od.service_name,mt.srctermid, od.feecode
) as success
left outer join
(
select count(*) as failNum, plat_mt.service_id, plat_mt.app_id, plat_mt.srctermid
from plat_mt, plat_orderservice
where plat_mt.occurtime >= @StartDate and plat_mt.occurtime <= @EndDate
and plat_mt.feetype = '02'
and plat_mt.morelatetomtflag_sgip = 0
and plat_mt.send_status <> 0
and plat_orderservice.service_id = plat_mt.service_id
group by plat_mt.service_id, plat_mt.app_id, plat_mt.srctermid
) as fail
on success.wapp_id = fail.app_id
and success.wservice_id = fail.service_id
and success.wsrctermid = fail.srctermid
goto endpos
errorpos:
rollback transaction
return
endpos:
commit transaction
return
GO
现在plat_mt表里面的数据比较多 超过100w条记录
当执行这个存储过程的时候,通常都会超时
还有 当我用delete from plat_mt where occurtime <'×××'的时候 也提示执行超时
删除操作失败
等待高手指点迷津