请帮忙优化一下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
SQL优化辅助工具(性能提高一倍以上)(需要源码请下载后联系我)
SQL优化辅助工具 针对IO开销很高的查询操作,生成索引优化SQL语句。 执行即可。 经测试(日访问10万左右的人才网,性能提高一倍)
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
SQL Server 2000优化SELECT语句方法
SQL Server 2000优化SELECT语句方法


疑难问题
