27,579
社区成员
发帖
与我相关
我的任务
分享那位能帮手优化下这个存储过程，数据量大时查询慢呀。
/*====查询*/
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 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
- 打赏
- 举报
这个语句看着都肯定很慢。解决方案：
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
