请帮忙优化一下SQL

naruto_zy naruto_zy 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
数据大概有20W左右,结果出来有点慢,希望帮忙优化一下,SQL如下:




CREATE procedure datagsreport @wtype varchar,@year int
as
set nocount on
create table #temp(custgroup varchar(50),customer varchar(50) ,pfamily varchar(50),pname varchar(50),
productno varchar(3),
okmyear int ,okmqur int ,factoryname varchar(200),uaescause varchar(500) ,dept varchar(500), improvement varchar(500),
implemdate datetime ,u8dreport varchar(500), pcs decimal(13),bnum decimal(13),cnum decimal(13),
snum decimal(13),qty decimal(13))

declare @cg varchar(50),@cm varchar(50),@pf varchar(50),@pm varchar(50),@tyear int ,@tqur int ,
@ps decimal(13),@bm decimal(13),@cnm decimal(13),@sm decimal(13),@qt decimal(13)
,@fc varchar(200),@us varchar(500),@im varchar(500),@imdate datetime,@dp varchar (500),@u8 varchar(500)

if @wtype='o'
begin
select
custgroup,customer,pfamily,pname,okmyear,okmqur ,isnull(sum(pcs),0) as pcs,
isnull(sum(bnum),0) as bnum,isnull(sum(cnum),0) as cnum,isnull(sum(snum),0) as
snum ,factoryname,isnull(sum(qty),0) as qty
into #ta from (
select
custgroup,customer,pfamily,pname,okmyear,okmqur,productno,factoryname,sum(pcs) as pcs, sum(bnum) as bnum,
sum(cnum) as cnum,
sum(snum) as snum ,case okmqur when 1 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 1 and 3)
when 2 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 4 and 6)
when 3 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 7 and 9)
when 4 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.okmyear and month(pdate) between 10 and 12)
end as qty from vw_datags ab
where typeno in (select typeno from reportswt where rid=24) and okmyear
between
@year-2 and @year
group by
custgroup,customer,pfamily,pname,productno,okmyear,okmqur,factoryname
) ac group by
custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname
order by custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname

declare cur cursor for
select custgroup,customer,pfamily,pname,okmyear,okmqur,factoryname,pcs,bnum,cnum,snum,qty from #ta
open cur
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
while @@fetch_status =0
begin
select top 1 @us=uaescause ,@im= improvement,@imdate =implemdate ,@dp=dept ,@u8=u8dreport from vw_datags bb
where typeno in (select typeno from reportswt where rid=24) and isnull(bb.custgroup,' ' )=isnull(@cg, ' ')
and isnull(bb.customer, ' ') =isnull( @cm, ' ') and isnull(bb.pfamily,' ') =isnull(@pf,' ' ) and
isnull(bb.pname,' ')=isnull(@pm,' ' ) and bb.okmyear= @tyear and bb.okmqur=@tqur
and isnull(bb.factoryname,' ' )=isnull(@fc, ' ')
group by uaescause ,dept,improvement,implemdate, u8dreport order by count(uaescause) desc

insert into #temp values (@cg,@cm,@pf,@pm,' ' ,@tyear,@tqur,@fc,@us ,@dp ,@im,@imdate,@u8,@ps,@bm,@cnm,@sm,@qt)
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
end
select * from #temp
end
else
begin
select
custgroup,customer,pfamily,pname,fieldyear,fieldqur ,isnull(sum(pcs),0) as pcs,
isnull(sum(bnum),0) as bnum,isnull(sum(cnum),0) as cnum,isnull(sum(snum),0) as
snum ,factoryname,isnull(sum(qty),0) as qty
into #tb from (
select
custgroup,customer,pfamily,pname,fieldyear,fieldqur,productno,factoryname,sum(pcs) as pcs, sum(bnum) as bnum,
sum(cnum) as cnum,
sum(snum) as snum ,case fieldqur when 1 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 1 and 3)
when 2 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 4 and 6)
when 3 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 7 and 9)
when 4 then (select sum(quantity) from sales where
productno=ab.productno
and year(pdate)=ab.fieldyear and month(pdate) between 10 and 12)
end as qty from vw_datags ab
where typeno in (select typeno from reportswt where rid=25) and fieldyear
between
@year-2 and @year
group by
custgroup,customer,pfamily,pname,productno,fieldyear,fieldqur,factoryname
) ac group by
custgroup,customer,pfamily,pname,fieldyear,fieldqur,factoryname
order by custgroup,customer,pfamily,pname,fieldyear,fieldqur,factoryname

declare cur cursor for
select custgroup,customer,pfamily,pname,fieldyear,fieldqur,factoryname,pcs,bnum,cnum,snum,qty from #tb
open cur
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
while @@fetch_status =0
begin
select top 1 @us=uaescause ,@im= improvement,@imdate =implemdate ,@dp=dept ,@u8=u8dreport from vw_datags bb
where typeno in (select typeno from reportswt where rid=25) and isnull(bb.custgroup,' ' )=isnull(@cg, ' ')
and isnull(bb.customer, ' ') =isnull( @cm, ' ') and isnull(bb.pfamily,' ') =isnull(@pf,' ' ) and
isnull(bb.pname,' ')=isnull(@pm,' ' ) and bb.fieldyear= @tyear and bb.fieldqur=@tqur
and isnull(bb.factoryname,' ' )=isnull(@fc, ' ')
group by uaescause ,dept,improvement,implemdate, u8dreport order by count(uaescause) desc

insert into #temp values (@cg,@cm,@pf,@pm,' ' ,@tyear,@tqur,@fc,@us ,@dp ,@im,@imdate,@u8,@ps,@bm,@cnm,@sm,@qt)
fetch next from cur into @cg,@cm,@pf,@pm,@tyear,@tqur,@fc,@ps,@bm,@cnm,@sm,@qt
end
select * from #temp
end
close cur
deallocate cur
drop table #temp
...全文

相关推荐

请参见:基于开源组件(Inception,SQLAdvisor和SOAR)SQL审核和SQL优化的Web平台

请参见SQL审核平台 API集成 Inception :去哪儿网开源,提供SQL语句审核,执行,回滚功能 SQLAdvisor :美团开源,提供分析SQL中的where条件,聚合条件,多表Join关系,输出索引优化建议 SOAR :小米开源,提供SQL启发式算法的语句优化,多列索引优化等功能 功能简介 目标库管理 支持多场地/数据中心的数据库管理,纳入方式归纳目标数据库 支持目标数据库配置,库/表结构查询 SQL操作 根据开始 SQL语法检测 SQL语句执行 SQL回滚 定时工单 历史记录 SQL查询 查询目标数据库的详细表结构 查询表数据,对结果可导出文件 SQL语句优化(基于美团SQLAd

Machinery Ly

SQL优化辅助工具(性能提高一倍以上)(需要源码请下载后联系我)

SQL优化辅助工具 针对IO开销很高的查询操作,生成索引优化SQL语句。 执行即可。 经测试(日访问10万左右的人才网,性能提高一倍)

QQ18795736

SQL编写规范和优化讲稿.pptx

SQL编写规范和优化讲稿.pptx

拉拉庸

Sql Server 索引使用情况及优化的相关Sql语句分享

代码如下: –Begin Index(索引) 分析优化的相关 Sql — 返回当前数据库所有碎片率大于25%的索引 — 运行本语句会扫描很多数据页面 — 避免在系统负载比较高时运行 — 避免在系统负载比较高时运行 declare @dbid int select @dbid = db_id() SELECT o.name as tablename,s.* FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL, NULL, NULL) s,sys.objects o where avg_fragmentation_in_percent>25

weixin_38742291

SQL Server 2000优化SELECT语句方法

SQL Server 2000优化SELECT语句方法

stonedog
评论 5
收藏 已收藏
分享
返回顶部
naruto_zy
疑难问题 22,299 成员 121,727 内容