34,588
社区成员
发帖
与我相关
我的任务
分享速度太慢了 这个存储过程如何优化
CREATE proc dbo.UP_RcptMng_DC_Login
@Para_ID int,
@Para_REQDATE datetime,
@Para_SuppCD int,
@Para_RESISTTIME datetime,
@Para_RCPTMNGSTATUS int
as
declare @InspDate datetime
declare @ProductCD varchar(13)
begin
-----------------------------------------------------------------
declare Product_cursor cursor
for
select
-- @InspDate=INSPDATE ,
-- @ProductCD = PRODUCTCD
INSPDATE,
PRODUCTCD
from
DBMainStream.dbo.TB_VENDORSTOCK
where
PURSTATUS in(0,1)
and
WORKFLOWREPRECD=10
and
convert(varchar(10),REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
SUPPCD = @Para_SuppCD
and
CONVERT(char(24),RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
open Product_cursor
fetch next from Product_cursor
into @InspDate,@ProductCD
while @@fetch_status = 0
begin
if exists
(
select null
from DBHTSystem.dbo.TB_ARRIVINSPREADY
where
convert(varchar(10),InspDate,120) like convert(varchar(10),@InspDate,120)
and
ProductCD=@ProductCD
and
SuppCD=@Para_SuppCD
and
convert(varchar(10),REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
)
begin
update
DBHTSystem.dbo.TB_ARRIVINSPREADY
set
OrderQty = OrderQty + (
select OrderQty
from
(
SELECT
k.INSPDATE,
k.PRODUCTCD,
k.SUPPCD,
k.REQDATE as ReqDate,
k.CASEINUNIT,
k.PRODUCTNAME,
sum(k.REQQTY) as OrderQty,
isnull(k.ITFCD,'') as ITFCD,
k.DIVISIONCD
,k.DEPARTMENTCD,
k.LINECD,
k.SUPPNAME,
k.WAREHOUSECD,
k.MAKERCD,
case
when lm.productcd is null or (lm.ShapeStatusCD=0 and lm.productcd is not null)
then 0
else 1
end as ProcPropertyRegType,
1 as ConsignerDivision,
@Para_ID as Auther,
getdate() as ResistTime
FROM
DBMainStream.dbo.TB_VENDORSTOCK k
left join
DBLocalMaster.dbo.LMST_WAREHOUSEPRODUCTS lm
on
lm.PRODUCTCD=k.PRODUCTCD
where
k.PRODUCTCD=@ProductCD
and
k.PURSTATUS in(0,1)
and
k.WORKFLOWREPRECD=10
and
convert(varchar(10),k.REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
k.SUPPCD = @Para_SuppCD
and
CONVERT(char(24),k.RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
k.RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
-- and not exists(
-- select tba.INSPDATE,
-- tba.PRODUCTCD,
-- tba.SUPPCD
-- from
-- DBHTSystem.dbo.TB_ARRIVINSPREADY as tba
-- where
-- k.INSPDATE=tba.INSPDATE
-- and
-- k.PRODUCTCD=tba.PRODUCTCD
-- and
-- k.SUPPCD=tba.SUPPCD
-- and
-- k.REQDATE=tba.ReqDate
-- )
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd,
lm.productcd,
lm.ShapeStatusCD
) as du
),
Auther = @Para_ID,
ResistTime =getdate()
end
else
begin
INSERT INTO DBHTSystem.dbo.TB_ARRIVINSPREADY
(InspDate,
ProductCD,
SuppCD,
ReqDate,
CaseInUnit,
ProductName,
OrderQty,
ITFCD,
DivisionCD,
DepartmentCD,
LineCD,
SuppName,
WarehouseCD,
MakerCD,
ProcPropertyRegType,
ConsignerDivision,
Auther,
ResistTime
)
SELECT
k.INSPDATE,
k.PRODUCTCD,
k.SUPPCD,
k.REQDATE as ReqDate,
k.CASEINUNIT,
k.PRODUCTNAME,
sum(k.REQQTY) as OrderQty,
isnull(k.ITFCD,'') as ITFCD,
k.DIVISIONCD
,k.DEPARTMENTCD,
k.LINECD,
k.SUPPNAME,
k.WAREHOUSECD,
k.MAKERCD,
case
when lm.productcd is null or (lm.ShapeStatusCD=0 and lm.productcd is not null)
then 0
else 1
end as ProcPropertyRegType,
1 as ConsignerDivision,
@Para_ID as Auther,
getdate() as ResistTime
FROM
DBMainStream.dbo.TB_VENDORSTOCK k
left join
DBLocalMaster.dbo.LMST_WAREHOUSEPRODUCTS lm
on
lm.PRODUCTCD=k.PRODUCTCD
where
k.PRODUCTCD=@ProductCD
and
k.PURSTATUS in(0,1)
and
k.WORKFLOWREPRECD=10
and
convert(varchar(10),k.REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
k.SUPPCD = @Para_SuppCD
and
CONVERT(char(24),k.RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
k.RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
-- and not exists(
-- select tba.INSPDATE,
-- tba.PRODUCTCD,
-- tba.SUPPCD
-- from
-- DBHTSystem.dbo.TB_ARRIVINSPREADY as tba
-- where
-- k.INSPDATE=tba.INSPDATE
-- and
-- k.PRODUCTCD=tba.PRODUCTCD
-- and
-- k.SUPPCD=tba.SUPPCD
-- and
-- k.REQDATE=tba.ReqDate
-- )
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd,
lm.productcd,
lm.ShapeStatusCD
end
fetch next from Product_cursor
into @InspDate,@ProductCD
end
close Product_cursor
DEALLOCATE Product_cursor
--★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆
insert into DBMainStream.dbo.TB_RESULTRCPTMNG_DC
(
Auther,
ConsignerDivision,
CREATETIME,
InspDate,
Maintainer,
ModifyTime,
RCPTMngStatus,
ReqDate,
ResistTime,
SuppCD,
WAREHOUSECD
)
select
distinct
AUTHER,
1,
RESISTTIME,
INSPDATE,
@Para_ID,
getdate(),
2,
REQDATE,
RESISTTIME,
SUPPCD,
WAREHOUSECD
from
DBMainStream.dbo.TB_VENDORSTOCK
where
convert(varchar(10),REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
SUPPCD = @Para_SuppCD
and
CONVERT(char(24),RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
update DBMainStream.dbo.TB_VENDORSTOCK
set
RCPTMNGSTATUS=2,
MAINTAINER = @Para_ID,
MODIFYTIME = getdate()
where
convert(varchar(10),REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
SUPPCD = @Para_SuppCD
and
CONVERT(char(24),RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
end
go
--★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆★☆
...全文
请发表友善的回复…
发表回复
wynlc 2008-09-28
- 打赏
- 举报
调调逻辑结构吧
teleinfor 2008-09-27
- 打赏
- 举报
那是相当的长啊... ...
wgzaaa 2008-09-27
- 打赏
- 举报
上面啥呀，优化完的？
shenbulongtongyaoyao 2008-09-27
- 打赏
- 举报
ALTER proc dbo.UP_RcptMng_DC_Login
@Para_ID int,
@Para_REQDATE datetime,
@Para_SuppCD int,
@Para_RESISTTIME datetime,
@Para_RCPTMNGSTATUS int
as
begin
----------------------------------------------
update
YAN
--DBHTSystem.dbo.TB_ARRIVINSPREADY
set
Auther = @Para_ID,
ResistTime =getdate(),
YAN.OrderQty =YAN.OrderQty+BO.OrderQty
from
DBHTSystem.dbo.TB_ARRIVINSPREADY YAN,
-- DBMainStream.dbo.TB_VENDORSTOCK BO,
-- DBLocalMaster.dbo.LMST_WAREHOUSEPRODUCTS YE
(
SELECT
k.INSPDATE INSPDATE,
k.PRODUCTCD PRODUCTCD,
k.SUPPCD SUPPCD,
k.REQDATE ReqDate,
k.CASEINUNIT CASEINUNIT,
-- k.PRODUCTNAME PRODUCTNAME,
sum(k.REQQTY) OrderQty,
isnull(k.ITFCD,'') ITFCD,
k.DIVISIONCD DIVISIONCD
,k.DEPARTMENTCD DEPARTMENTCD,
k.LINECD LINECD,
-- k.SUPPNAME SUPPNAME,
k.WAREHOUSECD WAREHOUSECD,
k.MAKERCD MAKERCD
-- case
-- when lm.productcd is null or (lm.ShapeStatusCD=0 and lm.productcd is not null)
-- then 0
-- else 1
-- end as ProcPropertyRegType,
-- 1 as ConsignerDivision,
-- @Para_ID as Auther,
-- getdate() as ResistTime
FROM
DBMainStream.dbo.TB_VENDORSTOCK k
left join
DBLocalMaster.dbo.LMST_WAREHOUSEPRODUCTS lm
on
lm.PRODUCTCD=k.PRODUCTCD
where
k.PURSTATUS in(0,1)
and
k.WORKFLOWREPRECD=10
and
convert(varchar(10),k.REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
k.SUPPCD = @Para_SuppCD
and
CONVERT(char(24),k.RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
k.RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
and exists(
select tba.INSPDATE,
tba.PRODUCTCD,
tba.SUPPCD
from
DBHTSystem.dbo.TB_ARRIVINSPREADY as tba
where
k.INSPDATE=tba.INSPDATE
and
k.PRODUCTCD=tba.PRODUCTCD
and
k.SUPPCD=tba.SUPPCD
and
k.REQDATE=tba.ReqDate
)
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
-- k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
-- k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd
) as BO
-- lm.productcd,
-- lm.ShapeStatusCD
where
YAN.SuppCD = BO.SUPPCD
and
YAN.ProductCD = BO.PRODUCTCD
-- and
-- BO.PRODUCTCD = YE.PRODUCTCD
and
YAN.SuppCD = @Para_SuppCD
and
YAN.InspDate = BO.INSPDATE
and
YAN.ReqDate = BO.ReqDate
and
convert(varchar(10),YAN.REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
YAN.CaseInUnit = BO.CASEINUNIT
and
YAN.MakerCD = BO.MAKERCD
and
YAN.LineCD = BO.LINECD
----------------------------------------------
INSERT INTO DBHTSystem.dbo.TB_ARRIVINSPREADY
(InspDate,
ProductCD,
SuppCD,
ReqDate,
CaseInUnit,
ProductName,
OrderQty,
ITFCD,
DivisionCD,
DepartmentCD,
LineCD,
SuppName,
WarehouseCD,
MakerCD,
ProcPropertyRegType,
ConsignerDivision,
Auther,
ResistTime
)
SELECT
k.INSPDATE,
k.PRODUCTCD,
k.SUPPCD,
k.REQDATE as ReqDate,
k.CASEINUNIT,
k.PRODUCTNAME,
sum(k.REQQTY) as OrderQty,
isnull(k.ITFCD,'') as ITFCD,
k.DIVISIONCD
,k.DEPARTMENTCD,
k.LINECD,
k.SUPPNAME,
k.WAREHOUSECD,
k.MAKERCD,
case
when lm.productcd is null or (lm.ShapeStatusCD=0 and lm.productcd is not null)
then 0
else 1
end as ProcPropertyRegType,
1 as ConsignerDivision,
@Para_ID as Auther,
getdate() as ResistTime
FROM
DBMainStream.dbo.TB_VENDORSTOCK k
left join
DBLocalMaster.dbo.LMST_WAREHOUSEPRODUCTS lm
on
lm.PRODUCTCD=k.PRODUCTCD
where
k.PURSTATUS in(0,1)
and
k.WORKFLOWREPRECD=10
and
convert(varchar(10),k.REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
k.SUPPCD = @Para_SuppCD
and
CONVERT(char(24),k.RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
k.RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
and not exists(
select tba.INSPDATE,
tba.PRODUCTCD,
tba.SUPPCD
from
DBHTSystem.dbo.TB_ARRIVINSPREADY as tba
where
k.INSPDATE=tba.INSPDATE
and
k.PRODUCTCD=tba.PRODUCTCD
and
k.SUPPCD=tba.SUPPCD
and
k.REQDATE=tba.ReqDate
)
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd,
lm.productcd,
lm.ShapeStatusCD
insert into DBMainStream.dbo.TB_RESULTRCPTMNG_DC
(
Auther,
ConsignerDivision,
CREATETIME,
InspDate,
Maintainer,
ModifyTime,
RCPTMngStatus,
ReqDate,
ResistTime,
SuppCD,
WAREHOUSECD
)
select
distinct
AUTHER,
1,
RESISTTIME,
INSPDATE,
@Para_ID,
getdate(),
2,
REQDATE,
RESISTTIME,
SUPPCD,
WAREHOUSECD
from
DBMainStream.dbo.TB_VENDORSTOCK
where
convert(varchar(10),REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
SUPPCD = @Para_SuppCD
and
CONVERT(char(24),RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
update DBMainStream.dbo.TB_VENDORSTOCK
set
RCPTMNGSTATUS=2,
MAINTAINER = @Para_ID,
MODIFYTIME = getdate()
where
convert(varchar(10),REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
SUPPCD = @Para_SuppCD
and
CONVERT(char(24),RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
end
GO
ws_hgo 2008-09-26
- 打赏
- 举报
长的很
victorcai2006 2008-09-26
- 打赏
- 举报
长～～～！！！在查询分析器里看看执行计划，分析一下
为什么不建一个临时表来处理？（建议）
存储过程不要写那么长，数据少看不出来，数据多了就恼火了
为什么不建一个临时表来处理？（建议）
存储过程不要写那么长，数据少看不出来，数据多了就恼火了
dobear_0922 2008-09-26
- 打赏
- 举报
[Quote=引用 1 楼 happyflystone 的回复:]
好长啊
[/Quote]
好长啊
[/Quote]
-晴天 2008-09-26
- 打赏
- 举报
else部分也是那么多的分组列,应该对速度产生较大的影响的
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd,
lm.productcd,
lm.ShapeStatusCD
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd,
lm.productcd,
lm.ShapeStatusCD
-晴天 2008-09-26
- 打赏
- 举报
可否改成:
select OrderQty
from
(
SELECT
sum(k.REQQTY) as OrderQty,
FROM
必要的连接表
group by
必要的分组条件
) as du
-晴天 2008-09-26
- 打赏
- 举报
select OrderQty
from
(
SELECT
k.INSPDATE,
k.PRODUCTCD,
k.SUPPCD,
k.REQDATE as ReqDate,
k.CASEINUNIT,
k.PRODUCTNAME,
sum(k.REQQTY) as OrderQty,
isnull(k.ITFCD,'') as ITFCD,
k.DIVISIONCD
,k.DEPARTMENTCD,
k.LINECD,
k.SUPPNAME,
k.WAREHOUSECD,
k.MAKERCD,
case
when lm.productcd is null or (lm.ShapeStatusCD=0 and lm.productcd is not null)
then 0
else 1
end as ProcPropertyRegType,
1 as ConsignerDivision,
@Para_ID as Auther,
getdate() as ResistTime
FROM
DBMainStream.dbo.TB_VENDORSTOCK k
left join
DBLocalMaster.dbo.LMST_WAREHOUSEPRODUCTS lm
on
lm.PRODUCTCD=k.PRODUCTCD
where
k.PRODUCTCD=@ProductCD
and
k.PURSTATUS in(0,1)
and
k.WORKFLOWREPRECD=10
and
convert(varchar(10),k.REQDATE,120) like convert(varchar(10),@Para_REQDATE,120)
and
k.SUPPCD = @Para_SuppCD
and
CONVERT(char(24),k.RESISTTIME,120) = CONVERT(char(24),@Para_RESISTTIME,120)
and
k.RCPTMNGSTATUS = @Para_RCPTMNGSTATUS
-- and not exists(
-- select tba.INSPDATE,
-- tba.PRODUCTCD,
-- tba.SUPPCD
-- from
-- DBHTSystem.dbo.TB_ARRIVINSPREADY as tba
-- where
-- k.INSPDATE=tba.INSPDATE
-- and
-- k.PRODUCTCD=tba.PRODUCTCD
-- and
-- k.SUPPCD=tba.SUPPCD
-- and
-- k.REQDATE=tba.ReqDate
-- )
group by
k.inspdate,
k.productcd,
k.suppcd,
k.REQDATE,
k.CaseInUnit,
k.productname,
k.ITFCD,
k.DivisionCD,
k.DepartmentCD,
k.LineCD,
k.SUPPNAME,
k.warehousecd,
k.MakerCD,
k.productcd,
lm.productcd,
lm.ShapeStatusCD
) as du
既然只要一个列,为什么在select 子句中要加上那么多列呢?
-狙击手- 2008-09-26
- 打赏
- 举报
好长
