那位能帮手优化下这个存储过程,数据量大时查询慢呀。

lifndcw 2017-08-29 10:57:34

/*====查询*/
CREATE PROCEDURE dbo.seek_dzb
@seek_date datetime, --开始日期
@seek_date1 datetime, --结束日期
@kh_no varchar(20) --客户编码
AS
declare @ys_date datetime
declare @ys_enddate datetime
set @ys_date=dateadd(day, - day(@seek_date) + 1, @seek_date)
set @ys_enddate=DATEADD(DD,-DAY(DATEADD(M,1,@seek_date1)),DATEADD(M,1,@seek_date1))

if exists (select * from tempdb..sysobjects where name like '##dz_t%') drop table ##dz_t

SELECT 客户编码,品名编码,品名,单价,工艺要求 into ##dz_t from dbo.dz where 客户编码=@kh_no and left(工艺要求,2)<>'每方' and 工艺要求<>'抛光单价' order by 客户编码,品名编码,工艺要求


SELECT a.客户编码,a.品名编码,a.品名,b.单位,isnull(a.单价,0) as 单价,a.工艺要求,
isnull(c.期初只数,0) as 期初只数,
isnull(d.来货只数,0) as 进货只数,
isnull(e.出货只数,0) as 出货只数,
isnull(d.返电只数,0) as 返电只数,
isnull(e.退回只数,0) as 退回只数,
isnull(e.黑胚退回只数,0) as 黑胚退回只数,
isnull(e.损耗退回只数,0) as 损耗退回只数,
round(isnull(c.期初只数,0)+isnull(d.来货只数,0)-isnull(e.出货只数,0)+isnull(d.返电只数,0)-isnull(e.退回只数,0)-isnull(e.黑胚退回只数,0)-isnull(e.损耗退回只数,0),2) as 期未只数,
(isnull(e.出货只数,0)-isnull(d.返电只数,0)) as 加工只数,(isnull(e.出货只数,0)-isnull(d.返电只数,0))*isnull(a.单价,0) as 加工金额,e.会计期
from ##dz_t a
FULL OUTER JOIN
(select dbo.pmgg.单位,pmgg.品名编码,pmgg.品名,pmgg.客户编码 from pmgg) b on a.品名编码 = b.品名编码 AND a.客户编码 = b.客户编码
left OUTER JOIN ---期初库存数
(select spkc.客户编码,spkc.品名编码,spkc.工艺要求,spkc.会计期,sum(spkc.期初只数) AS 期初只数 from spkc
where spkc.会计期=@ys_date group by spkc.客户编码,spkc.品名编码,spkc.工艺要求,spkc.会计期) c on a.客户编码 = c.客户编码 and a.品名编码 = c.品名编码 and a.工艺要求=c.工艺要求

left OUTER JOIN --进仓数
(SELECT lhm.客户编码,lhs.品名编码,lhs.工艺要求,lhs.会计期,
sum(case when lhs.收货类型='' then lhs.只数 else 0 end) AS 来货只数,
sum(case when lhs.收货类型='返工' then lhs.只数 else 0 end) AS 返电只数
FROM dbo.lhm INNER JOIN
dbo.lhs ON dbo.lhm.id = dbo.lhs.idd where dbo.lhm.来货日期 BETWEEN @ys_date and @ys_enddate
GROUP BY lhm.客户编码,lhs.品名编码,lhs.工艺要求,lhs.会计期) d ON a.客户编码 = d.客户编码 and a.品名编码 = d.品名编码 and a.工艺要求=d.工艺要求

left OUTER JOIN --出仓数
(SELECT chm.客户编码,chs.品名编码,chs.工艺要求,chs.会计期,
sum(case when chs.送货类型='' then dbo.chs.只数 else 0 end) AS 出货只数,
sum(case when chs.送货类型='退货' then dbo.chs.只数 else 0 end) AS 退回只数,
sum(case when chs.送货类型='黑胚退回' then dbo.chs.只数 else 0 end) AS 黑胚退回只数,
sum(case when chs.送货类型='损耗退回' then dbo.chs.只数 else 0 end) AS 损耗退回只数

FROM dbo.chm INNER JOIN
dbo.chs ON dbo.chm.id = dbo.chs.idd where dbo.chm.出货日期 BETWEEN @ys_date and @ys_enddate
GROUP BY chm.客户编码,chs.品名编码,chs.工艺要求,chs.会计期) e ON a.客户编码 = e.客户编码 and a.品名编码 = e.品名编码 and a.工艺要求=e.工艺要求


--where a.工艺要求 in (select 内容 from para_pz) and a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and f.返电只数=0)
where a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and d.返电只数=0 and e.退回只数=0 and e.黑胚退回只数=0 and e.损耗退回只数=0)
order by a.客户编码,a.品名编码,a.工艺要求
if exists (select * from tempdb..sysobjects where name like '##dz_t%') drop table ##dz_t
GO
...全文
123 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
顺势而为1 2017-08-29
  • 打赏
  • 举报
回复
楼主怎么用中文作字段啊,为什么不用英文或拼音呢
lifndcw 2017-08-29
  • 打赏
  • 举报
回复
现用以下的方式,速度快了三分之一,帮手看下,还能优化吗 /*====查询对帐单*/ CREATE PROCEDURE dbo.seek_dzb @seek_date datetime, --开始日期 @seek_date1 datetime, --结束日期 @kh_no varchar(20) --客户编码 AS declare @ys_date datetime declare @ys_enddate datetime set @ys_date=dateadd(day, - day(@seek_date) + 1, @seek_date) set @ys_enddate=DATEADD(DD,-DAY(DATEADD(M,1,@seek_date1)),DATEADD(M,1,@seek_date1)) if exists (select * from tempdb..sysobjects where name like '##dz_t%') drop table ##dz_t if exists (select * from tempdb..sysobjects where name like '##pmgg_t%') drop table ##pmgg_t if exists (select * from tempdb..sysobjects where name like '##spkc_t%') drop table ##spkc_t if exists (select * from tempdb..sysobjects where name like '##lhm_t%') drop table ##lhm_t if exists (select * from tempdb..sysobjects where name like '##chm_t%') drop table ##chm_t SELECT 客户编码,品名编码,品名,单价,工艺要求 into ##dz_t from dbo.dz where 客户编码=@kh_no and left(工艺要求,2)<>'每方' and 工艺要求<>'抛光单价' order by 客户编码,品名编码,工艺要求 select dbo.pmgg.单位,pmgg.品名编码,pmgg.品名,pmgg.客户编码 into ##pmgg_t from pmgg where 客户编码=@kh_no select spkc.客户编码,spkc.品名编码,spkc.工艺要求,spkc.会计期,sum(spkc.期初只数) AS 期初只数 into ##spkc_t from spkc where spkc.会计期=@ys_date and 客户编码=@kh_no group by spkc.客户编码,spkc.品名编码,spkc.工艺要求,spkc.会计期 SELECT lhm.客户编码,lhs.品名编码,lhs.工艺要求,lhs.会计期, sum(case when lhs.收货类型='' then lhs.只数 else 0 end) AS 来货只数, sum(case when lhs.收货类型='返工' then lhs.只数 else 0 end) AS 返电只数 into ##lhm_t FROM dbo.lhm INNER JOIN dbo.lhs ON dbo.lhm.id = dbo.lhs.idd where lhm.客户编码=@kh_no and dbo.lhm.来货日期 BETWEEN @ys_date and @ys_enddate GROUP BY lhm.客户编码,lhs.品名编码,lhs.工艺要求,lhs.会计期 sELECT chm.客户编码,chs.品名编码,chs.工艺要求,chs.会计期, sum(case when chs.送货类型='' then dbo.chs.只数 else 0 end) AS 出货只数, sum(case when chs.送货类型='退货' then dbo.chs.只数 else 0 end) AS 退回只数, sum(case when chs.送货类型='黑胚退回' then dbo.chs.只数 else 0 end) AS 黑胚退回只数, sum(case when chs.送货类型='损耗退回' then dbo.chs.只数 else 0 end) AS 损耗退回只数 into ##chm_t FROM dbo.chm INNER JOIN dbo.chs ON dbo.chm.id = dbo.chs.idd where chm.客户编码=@kh_no and dbo.chm.出货日期 BETWEEN @ys_date and @ys_enddate GROUP BY chm.客户编码,chs.品名编码,chs.工艺要求,chs.会计期 SELECT a.客户编码,a.品名编码,a.品名,b.单位,isnull(a.单价,0) as 单价,a.工艺要求, isnull(c.期初只数,0) as 期初只数, isnull(d.来货只数,0) as 进货只数, isnull(e.出货只数,0) as 出货只数, isnull(d.返电只数,0) as 返电只数, isnull(e.退回只数,0) as 退回只数, isnull(e.黑胚退回只数,0) as 黑胚退回只数, isnull(e.损耗退回只数,0) as 损耗退回只数, round(isnull(c.期初只数,0)+isnull(d.来货只数,0)-isnull(e.出货只数,0)+isnull(d.返电只数,0)-isnull(e.退回只数,0)-isnull(e.黑胚退回只数,0)-isnull(e.损耗退回只数,0),2) as 期未只数, (isnull(e.出货只数,0)-isnull(d.返电只数,0)) as 加工只数,(isnull(e.出货只数,0)-isnull(d.返电只数,0))*isnull(a.单价,0) as 加工金额,e.会计期 from ##dz_t a FULL OUTER JOIN (select * from ##pmgg_t) b on a.品名编码 = b.品名编码 AND a.客户编码 = b.客户编码 left OUTER JOIN ---期初库存数 (select * from ##spkc_t) c on a.客户编码 = c.客户编码 and a.品名编码 = c.品名编码 and a.工艺要求=c.工艺要求 left OUTER JOIN --进仓数 (select * from ##lhm_t) d ON a.客户编码 = d.客户编码 and a.品名编码 = d.品名编码 and a.工艺要求=d.工艺要求 left OUTER JOIN --出仓数 (select * from ##chm_t) e ON a.客户编码 = e.客户编码 and a.品名编码 = e.品名编码 and a.工艺要求=e.工艺要求 --where a.工艺要求 in (select 内容 from para_pz) and a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and f.返电只数=0) where a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and d.返电只数=0 and e.退回只数=0 and e.黑胚退回只数=0 and e.损耗退回只数=0) order by a.客户编码,a.品名编码,a.工艺要求 if exists (select * from tempdb..sysobjects where name like '##dz_t%') drop table ##dz_t if exists (select * from tempdb..sysobjects where name like '##pmgg_t%') drop table ##pmgg_t if exists (select * from tempdb..sysobjects where name like '##spkc_t%') drop table ##spkc_t if exists (select * from tempdb..sysobjects where name like '##lhm_t%') drop table ##lhm_t if exists (select * from tempdb..sysobjects where name like '##chm_t%') drop table ##chm_t GO
OwenZeng_DBA 2017-08-29
  • 打赏
  • 举报
回复
引用 楼主 lifndcw 的回复:
/*====查询*/ CREATE PROCEDURE dbo.seek_dzb @seek_date datetime, --开始日期 @seek_date1 datetime, --结束日期 @kh_no varchar(20) --客户编码 AS declare @ys_date datetime declare @ys_enddate datetime set @ys_date=dateadd(day, - day(@seek_date) + 1, @seek_date) set @ys_enddate=DATEADD(DD,-DAY(DATEADD(M,1,@seek_date1)),DATEADD(M,1,@seek_date1)) if exists (select * from tempdb..sysobjects where name like '##dz_t%') drop table ##dz_t SELECT 客户编码,品名编码,品名,单价,工艺要求 into ##dz_t from dbo.dz where 客户编码=@kh_no and left(工艺要求,2)<>'每方' and 工艺要求<>'抛光单价' order by 客户编码,品名编码,工艺要求 SELECT a.客户编码,a.品名编码,a.品名,b.单位,isnull(a.单价,0) as 单价,a.工艺要求, isnull(c.期初只数,0) as 期初只数, isnull(d.来货只数,0) as 进货只数, isnull(e.出货只数,0) as 出货只数, isnull(d.返电只数,0) as 返电只数, isnull(e.退回只数,0) as 退回只数, isnull(e.黑胚退回只数,0) as 黑胚退回只数, isnull(e.损耗退回只数,0) as 损耗退回只数, round(isnull(c.期初只数,0)+isnull(d.来货只数,0)-isnull(e.出货只数,0)+isnull(d.返电只数,0)-isnull(e.退回只数,0)-isnull(e.黑胚退回只数,0)-isnull(e.损耗退回只数,0),2) as 期未只数, (isnull(e.出货只数,0)-isnull(d.返电只数,0)) as 加工只数,(isnull(e.出货只数,0)-isnull(d.返电只数,0))*isnull(a.单价,0) as 加工金额,e.会计期 from ##dz_t a FULL OUTER JOIN (select dbo.pmgg.单位,pmgg.品名编码,pmgg.品名,pmgg.客户编码 from pmgg) b on a.品名编码 = b.品名编码 AND a.客户编码 = b.客户编码 left OUTER JOIN ---期初库存数 (select spkc.客户编码,spkc.品名编码,spkc.工艺要求,spkc.会计期,sum(spkc.期初只数) AS 期初只数 from spkc where spkc.会计期=@ys_date group by spkc.客户编码,spkc.品名编码,spkc.工艺要求,spkc.会计期) c on a.客户编码 = c.客户编码 and a.品名编码 = c.品名编码 and a.工艺要求=c.工艺要求 left OUTER JOIN --进仓数 (SELECT lhm.客户编码,lhs.品名编码,lhs.工艺要求,lhs.会计期, sum(case when lhs.收货类型='' then lhs.只数 else 0 end) AS 来货只数, sum(case when lhs.收货类型='返工' then lhs.只数 else 0 end) AS 返电只数 FROM dbo.lhm INNER JOIN dbo.lhs ON dbo.lhm.id = dbo.lhs.idd where dbo.lhm.来货日期 BETWEEN @ys_date and @ys_enddate GROUP BY lhm.客户编码,lhs.品名编码,lhs.工艺要求,lhs.会计期) d ON a.客户编码 = d.客户编码 and a.品名编码 = d.品名编码 and a.工艺要求=d.工艺要求 left OUTER JOIN --出仓数 (SELECT chm.客户编码,chs.品名编码,chs.工艺要求,chs.会计期, sum(case when chs.送货类型='' then dbo.chs.只数 else 0 end) AS 出货只数, sum(case when chs.送货类型='退货' then dbo.chs.只数 else 0 end) AS 退回只数, sum(case when chs.送货类型='黑胚退回' then dbo.chs.只数 else 0 end) AS 黑胚退回只数, sum(case when chs.送货类型='损耗退回' then dbo.chs.只数 else 0 end) AS 损耗退回只数 FROM dbo.chm INNER JOIN dbo.chs ON dbo.chm.id = dbo.chs.idd where dbo.chm.出货日期 BETWEEN @ys_date and @ys_enddate GROUP BY chm.客户编码,chs.品名编码,chs.工艺要求,chs.会计期) e ON a.客户编码 = e.客户编码 and a.品名编码 = e.品名编码 and a.工艺要求=e.工艺要求 --where a.工艺要求 in (select 内容 from para_pz) and a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and f.返电只数=0) where a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and d.返电只数=0 and e.退回只数=0 and e.黑胚退回只数=0 and e.损耗退回只数=0) order by a.客户编码,a.品名编码,a.工艺要求 if exists (select * from tempdb..sysobjects where name like '##dz_t%') drop table ##dz_t GO
这个语句看着都肯定很慢。解决方案: 1.left OUTER JOIN 都是需要outer join 吗,从业务逻辑上看看是不是可以调整下 2. 对查询进行下拆分,把部分结果存储到临时表,然后再和其他查询进行join
RINK_1 2017-08-29
  • 打赏
  • 举报
回复
插入第一个临时表#dz_t时,ORDER BY没多大意义吧。
吉普赛的歌 2017-08-29
  • 打赏
  • 举报
回复
/*==== 查询对帐单 */
CREATE PROCEDURE dbo.seek_dzb
	@seek_date DATETIME, --开始日期
	@seek_date1 DATETIME, --结束日期
	@kh_no VARCHAR(20) --客户编码
AS
BEGIN
	DECLARE @ys_date DATETIME
	DECLARE @ys_enddate DATETIME
	SET @ys_date = DATEADD(DAY, - DAY(@seek_date) + 1, @seek_date)
	SET @ys_enddate = DATEADD(
	        DD,
	        -DAY(DATEADD(M, 1, @seek_date1)),
	        DATEADD(M, 1, @seek_date1)
	    )
	
	--没必要用上全局临时表,全部改成临时表,存储过程内部使用之前不需要判断
	
	SELECT 客户编码,
	       品名编码,
	       品名,
	       单价,
	       工艺要求
	INTO   #dz_t
	FROM   dbo.dz
	WHERE  客户编码 = @kh_no
	       AND LEFT(工艺要求, 2) <> '每方'
	       AND 工艺要求 <> '抛光单价'
	ORDER BY
	       客户编码,
	       品名编码,
	       工艺要求
	--加索引
	CREATE CLUSTERED INDEX IX_dz_t ON #dz_t([客户编码],[品名编码],[工艺要求])
	
	SELECT dbo.pmgg.单位,
	       pmgg.品名编码,
	       pmgg.品名,
	       pmgg.客户编码
	INTO   #pmgg_t
	FROM   pmgg
	WHERE  客户编码 = @kh_no
	--加索引
	CREATE CLUSTERED INDEX IX_pmgg_t ON #pmgg_t([客户编码],[品名编码],[工艺要求])
	
	SELECT spkc.客户编码,
	       spkc.品名编码,
	       spkc.工艺要求,
	       spkc.会计期,
	       SUM(spkc.期初只数) AS 期初只数
	INTO   #spkc_t
	FROM   spkc
	WHERE  spkc.会计期 = @ys_date
	       AND 客户编码 = @kh_no
	GROUP BY
	       spkc.客户编码,
	       spkc.品名编码,
	       spkc.工艺要求,
	       spkc.会计期
	--加索引
	CREATE CLUSTERED INDEX IX_spkc_t ON #spkc_t([客户编码],[品名编码],[工艺要求])
	
	SELECT lhm.客户编码,
	       lhs.品名编码,
	       lhs.工艺要求,
	       lhs.会计期,
	       SUM(CASE WHEN lhs.收货类型 = '' THEN lhs.只数 ELSE 0 END) AS 来货只数,
	       SUM(CASE WHEN lhs.收货类型 = '返工' THEN lhs.只数 ELSE 0 END) AS 
	       返电只数
	INTO   #lhm_t
	FROM   dbo.lhm
	       INNER JOIN dbo.lhs
	            ON  dbo.lhm.id = dbo.lhs.idd
	WHERE  lhm.客户编码 = @kh_no
	       AND dbo.lhm.来货日期 BETWEEN @ys_date AND @ys_enddate
	GROUP BY
	       lhm.客户编码,
	       lhs.品名编码,
	       lhs.工艺要求,
	       lhs.会计期
	--加索引
	CREATE CLUSTERED INDEX IX_lhm_t ON #lhm_t([客户编码],[品名编码],[工艺要求])
	
	SELECT chm.客户编码,
	       chs.品名编码,
	       chs.工艺要求,
	       chs.会计期,
	       SUM(CASE WHEN chs.送货类型 = '' THEN dbo.chs.只数 ELSE 0 END) AS 
	       出货只数,
	       SUM(
	           CASE 
	                WHEN chs.送货类型 = '退货' THEN dbo.chs.只数
	                ELSE 0
	           END
	       )  AS 退回只数,
	       SUM(
	           CASE 
	                WHEN chs.送货类型 = '黑胚退回' THEN dbo.chs.只数
	                ELSE 0
	           END
	       )  AS 黑胚退回只数,
	       SUM(
	           CASE 
	                WHEN chs.送货类型 = '损耗退回' THEN dbo.chs.只数
	                ELSE 0
	           END
	       )  AS 损耗退回只数
	INTO   #chm_t
	FROM   dbo.chm
	       INNER JOIN dbo.chs
	            ON  dbo.chm.id = dbo.chs.idd
	WHERE  chm.客户编码 = @kh_no
	       AND dbo.chm.出货日期 BETWEEN @ys_date AND @ys_enddate
	GROUP BY
	       chm.客户编码,
	       chs.品名编码,
	       chs.工艺要求,
	       chs.会计期
	--加索引
	CREATE CLUSTERED INDEX IX_chm_t ON #chm_t([客户编码],[品名编码],[工艺要求])
	
	SELECT a.客户编码,
	       a.品名编码,
	       a.品名,
	       b.单位,
	       ISNULL(a.单价, 0)          AS 单价,
	       a.工艺要求,
	       ISNULL(c.期初只数, 0)      AS 期初只数,
	       ISNULL(d.来货只数, 0)      AS 进货只数,
	       ISNULL(e.出货只数, 0)      AS 出货只数,
	       ISNULL(d.返电只数, 0)      AS 返电只数,
	       ISNULL(e.退回只数, 0)      AS 退回只数,
	       ISNULL(e.黑胚退回只数, 0)  AS 黑胚退回只数,
	       ISNULL(e.损耗退回只数, 0)  AS 损耗退回只数,
	       ROUND(
	           ISNULL(c.期初只数, 0) + ISNULL(d.来货只数, 0) -ISNULL(e.出货只数, 0)
	           + ISNULL(d.返电只数, 0) -ISNULL(e.退回只数, 0) -ISNULL(e.黑胚退回只数, 0)
	           -ISNULL(e.损耗退回只数, 0),
	           2
	       )                          AS 期未只数,
	       (ISNULL(e.出货只数, 0) -ISNULL(d.返电只数, 0)) AS 加工只数,
	       (ISNULL(e.出货只数, 0) -ISNULL(d.返电只数, 0)) * ISNULL(a.单价, 0) AS 
	       加工金额,
	       e.会计期
	FROM   #dz_t a
	       FULL OUTER JOIN #pmgg_t AS b
	            ON  a.品名编码 = b.品名编码
	            AND a.客户编码 = b.客户编码
	       LEFT OUTER JOIN ---期初库存数
	            #spkc_t AS c
	            ON  a.客户编码 = c.客户编码
	            AND a.品名编码 = c.品名编码
	            AND a.工艺要求 = c.工艺要求
	       LEFT OUTER JOIN --进仓数
	            #lhm_t AS d
	            ON  a.客户编码 = d.客户编码
	            AND a.品名编码 = d.品名编码
	            AND a.工艺要求 = d.工艺要求
	       LEFT OUTER JOIN --出仓数
	            #chm_t AS e
	            ON  a.客户编码 = e.客户编码
	            AND a.品名编码 = e.品名编码
	            AND a.工艺要求 = e.工艺要求
	                
	                
	                --where a.工艺要求 in (select 内容 from para_pz) and a.客户编码=@kh_no and not (c.期初只数=0 and d.来货只数=0 and e.出货只数=0 and f.返电只数=0)
	WHERE  
	       1=1
		   --AND a.客户编码 = @kh_no   --前面的 #dz_t a 提前过滤了,不应该再放到这里来
	       AND NOT (
	               c.期初只数 = 0
	               AND d.来货只数 = 0
	               AND e.出货只数 = 0
	               AND d.返电只数 = 0
	               AND e.退回只数 = 0
	               AND e.黑胚退回只数 = 0
	               AND e.损耗退回只数 = 0
	           )
	ORDER BY
	       a.客户编码,
	       a.品名编码,
	       a.工艺要求
	
	--不需要判断,这些表肯定是存在的
	DROP TABLE #dz_t,#pmgg_t,#spkc_t,#lhm_t,#chm_t
END
GO

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧