存储过程1:/*计算小时平均产量*/
CREATE PROCEDURE [dbo].[udt_avghourcount]
@startdate datetime,@enddate datetime
AS
Set Nocount On
/*@days 两日期之间有产量的天数*/
declare @days int
select @days=count([date]) from(
select DISTINCT convert(varchar(10),recordtime,23) as [date]
from tbl_definetimerecord
where recordtime between @startdate and dateadd(d,1,@enddate)) as p0 select * into #tmp from(select workstage,convert(varchar(13),recordtime,20) as [time],
count(engid)*1.0 as total
from tbl_definetimerecord
where recordtime between @startdate and dateadd(d,1,@enddate)
group by convert(varchar(13),recordtime,20) ,workstage) as p1 select * into #a from (select top 100 percent workstage,right(time,2) as [hour],cast(sum(total)*1.0/(@days*1.0)
as decimal(5,2)) as averagecount
from #tmp
group by workstage,right(time,2)
order by workstage) as p2 declare @sql varchar(8000)
set @sql='select case workstage
when ''1030'' then ''内装上线''
when ''1450'' then ''内装下线''
when ''3010'' then ''外装上线''
when ''3310'' then ''外装下线''
when ''4300'' then ''预装下线''
when ''6040'' then ''装风扇''
when ''6140'' then ''包装''
else workstage end as 工序,'
set @sql=@sql+'sum(case [hour] when ''00'' then averagecount else 0 end) as ''08:30'','
set @sql=@sql+'sum(case [hour] when ''01'' then averagecount else 0 end) as ''09:30'','
set @sql=@sql+'sum(case [hour] when ''02'' then averagecount else 0 end) as ''10:30'','
set @sql=@sql+'sum(case [hour] when ''03'' then averagecount else 0 end) as ''11:30'','
set @sql=@sql+'sum(case [hour] when ''04'' then averagecount else 0 end) as ''12:30'','
set @sql=@sql+'sum(case [hour] when ''05'' then averagecount else 0 end) as ''13:30'','
set @sql=@sql+'sum(case [hour] when ''06'' then averagecount else 0 end) as ''14:30'','
set @sql=@sql+'sum(case [hour] when ''07'' then averagecount else 0 end) as ''15:30'','
set @sql=@sql+'sum(case [hour] when ''08'' then averagecount else 0 end) as ''16:30'','
set @sql=@sql+'sum(case [hour] when ''09'' then averagecount else 0 end) as ''17:30'','
set @sql=@sql+'sum(case [hour] when ''10'' then averagecount else 0 end) as ''18:30'','
set @sql=@sql+'sum(case [hour] when ''11'' then averagecount else 0 end) as ''19:30'','
set @sql=@sql+'sum(case [hour] when ''12'' then averagecount else 0 end) as ''20:30'','
set @sql=@sql+'sum(case [hour] when ''13'' then averagecount else 0 end) as ''21:30'','
set @sql=@sql+'sum(case [hour] when ''14'' then averagecount else 0 end) as ''22:30'','
set @sql=@sql+'sum(case [hour] when ''15'' then averagecount else 0 end) as ''23:30'','
set @sql=@sql+'sum(case [hour] when ''16'' then averagecount else 0 end) as ''00:30'','
set @sql=@sql+'sum(case [hour] when ''17'' then averagecount else 0 end) as ''01:30'','
set @sql=@sql+'sum(case [hour] when ''18'' then averagecount else 0 end) as ''02:30'','
set @sql=@sql+'sum(case [hour] when ''19'' then averagecount else 0 end) as ''03:30'','
set @sql=@sql+'sum(case [hour] when ''20'' then averagecount else 0 end) as ''04:30'','
set @sql=@sql+'sum(case [hour] when ''21'' then averagecount else 0 end) as ''05:30'','
set @sql=@sql+'sum(case [hour] when ''22'' then averagecount else 0 end) as ''06:30'','
set @sql=@sql+'sum(case [hour] when ''23'' then averagecount else 0 end) as ''07:30'','
set @sql=left(@sql,len(@sql)-1)+ 'from #a group by workstage'
select @sql= 'select *,([08:30]+[09:30]+[10:30]+[11:30]+[12:30]+[13:30]+[14:30]+[15:30]
+[16:30]+[17:30]+[18:30]+[19:30]+[20:30]+[21:30]+[22:30]+[23:30]+[00:30]+[01:30]+[02:30]
+[03:30]+[04:30]+[05:30]+[06:30]+[07:30]) as 汇总 from ('+@sql+') as p3'
exec(@sql)
drop table #tmp
drop table #a
GO
存储过程2:/*订单查询*/
CREATE proc [dbo].[udt_order_workstage_count]
@orderid varchar(12),@startday varchar(10),@endday varchar(10)
as
Set Nocount On
declare @sql varchar(8000)
set @sql = 'select * into #a from (select id as 序号,OrderId as 订单号,'
select @sql = @sql + 'sum(case workstage when '''+workstage+'''
then number else 0 end) as '''+workstage+''','
from (select distinct workstage from v_order_workstage_count) as p0
if @orderid=null and @startday=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where convert(varchar(8),left(序号,8),112) between convert(varchar(8),dateadd(d,-4,getdate()),112)
and convert(varchar(8),getdate(),112)'
else
if @orderid!=null and @startday!=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where 订单号='''+ @orderid +''' and convert(varchar(8),left(序号,8),112) between
convert(varchar(8),cast('''+@startday+''' as datetime),112) and convert(varchar(8),cast('''+@endday+''' as datetime) ,112)'
else
if @orderid=null and @startday!=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where convert(varchar(8),left(序号,8),112) between convert(varchar(8),cast('''+@startday+''' as datetime),112)
and convert(varchar(8),cast('''+@endday+''' as datetime),112)'
else
if @orderid!=null and @startday=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where 订单号='''+ @orderid +''''
select @sql=@sql+'select distinct #a.*,engType as 机型
from #a left JOIN dbo.tbl_planOrder ON dbo.tbl_planOrder.orderid = #a.订单号
union all
select null,''汇总'',sum([ 订单数量]),sum([1030]),sum([1450]),sum([3010]),sum([3310]),sum([4300]),sum([6140]),null from #a
order by 序号 desc
drop table #a'
exec(@sql)
GO
优化存储过程SQL
CREATE PROCEDURE [dbo].[udt_avghourcount]
@startdate datetime,@enddate datetime
AS
Set Nocount On
/*@days 两日期之间有产量的天数*/
declare @days int
select @days=count([date]) from(
select DISTINCT convert(varchar(10),recordtime,23) as [date]
from tbl_definetimerecord
where recordtime between @startdate and dateadd(d,1,@enddate)) as p0 select * into #tmp from(select workstage,convert(varchar(13),recordtime,20) as [time],
count(engid)*1.0 as total
from tbl_definetimerecord
where recordtime between @startdate and dateadd(d,1,@enddate)
group by convert(varchar(13),recordtime,20) ,workstage) as p1 select * into #a from (select top 100 percent workstage,right(time,2) as [hour],cast(sum(total)*1.0/(@days*1.0)
as decimal(5,2)) as averagecount
from #tmp
group by workstage,right(time,2)
order by workstage) as p2 declare @sql varchar(8000)
set @sql='select case workstage
when ''1030'' then ''内装上线''
when ''1450'' then ''内装下线''
when ''3010'' then ''外装上线''
when ''3310'' then ''外装下线''
when ''4300'' then ''预装下线''
when ''6040'' then ''装风扇''
when ''6140'' then ''包装''
else workstage end as 工序,'
set @sql=@sql+'sum(case [hour] when ''00'' then averagecount else 0 end) as ''08:30'','
set @sql=@sql+'sum(case [hour] when ''01'' then averagecount else 0 end) as ''09:30'','
set @sql=@sql+'sum(case [hour] when ''02'' then averagecount else 0 end) as ''10:30'','
set @sql=@sql+'sum(case [hour] when ''03'' then averagecount else 0 end) as ''11:30'','
set @sql=@sql+'sum(case [hour] when ''04'' then averagecount else 0 end) as ''12:30'','
set @sql=@sql+'sum(case [hour] when ''05'' then averagecount else 0 end) as ''13:30'','
set @sql=@sql+'sum(case [hour] when ''06'' then averagecount else 0 end) as ''14:30'','
set @sql=@sql+'sum(case [hour] when ''07'' then averagecount else 0 end) as ''15:30'','
set @sql=@sql+'sum(case [hour] when ''08'' then averagecount else 0 end) as ''16:30'','
set @sql=@sql+'sum(case [hour] when ''09'' then averagecount else 0 end) as ''17:30'','
set @sql=@sql+'sum(case [hour] when ''10'' then averagecount else 0 end) as ''18:30'','
set @sql=@sql+'sum(case [hour] when ''11'' then averagecount else 0 end) as ''19:30'','
set @sql=@sql+'sum(case [hour] when ''12'' then averagecount else 0 end) as ''20:30'','
set @sql=@sql+'sum(case [hour] when ''13'' then averagecount else 0 end) as ''21:30'','
set @sql=@sql+'sum(case [hour] when ''14'' then averagecount else 0 end) as ''22:30'','
set @sql=@sql+'sum(case [hour] when ''15'' then averagecount else 0 end) as ''23:30'','
set @sql=@sql+'sum(case [hour] when ''16'' then averagecount else 0 end) as ''00:30'','
set @sql=@sql+'sum(case [hour] when ''17'' then averagecount else 0 end) as ''01:30'','
set @sql=@sql+'sum(case [hour] when ''18'' then averagecount else 0 end) as ''02:30'','
set @sql=@sql+'sum(case [hour] when ''19'' then averagecount else 0 end) as ''03:30'','
set @sql=@sql+'sum(case [hour] when ''20'' then averagecount else 0 end) as ''04:30'','
set @sql=@sql+'sum(case [hour] when ''21'' then averagecount else 0 end) as ''05:30'','
set @sql=@sql+'sum(case [hour] when ''22'' then averagecount else 0 end) as ''06:30'','
set @sql=@sql+'sum(case [hour] when ''23'' then averagecount else 0 end) as ''07:30'','
set @sql=left(@sql,len(@sql)-1)+ 'from #a group by workstage'
select @sql= 'select *,([08:30]+[09:30]+[10:30]+[11:30]+[12:30]+[13:30]+[14:30]+[15:30]
+[16:30]+[17:30]+[18:30]+[19:30]+[20:30]+[21:30]+[22:30]+[23:30]+[00:30]+[01:30]+[02:30]
+[03:30]+[04:30]+[05:30]+[06:30]+[07:30]) as 汇总 from ('+@sql+') as p3'
exec(@sql)
drop table #tmp
drop table #a
GO
存储过程2:/*订单查询*/
CREATE proc [dbo].[udt_order_workstage_count]
@orderid varchar(12),@startday varchar(10),@endday varchar(10)
as
Set Nocount On
declare @sql varchar(8000)
set @sql = 'select * into #a from (select id as 序号,OrderId as 订单号,'
select @sql = @sql + 'sum(case workstage when '''+workstage+'''
then number else 0 end) as '''+workstage+''','
from (select distinct workstage from v_order_workstage_count) as p0
if @orderid=null and @startday=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where convert(varchar(8),left(序号,8),112) between convert(varchar(8),dateadd(d,-4,getdate()),112)
and convert(varchar(8),getdate(),112)'
else
if @orderid!=null and @startday!=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where 订单号='''+ @orderid +''' and convert(varchar(8),left(序号,8),112) between
convert(varchar(8),cast('''+@startday+''' as datetime),112) and convert(varchar(8),cast('''+@endday+''' as datetime) ,112)'
else
if @orderid=null and @startday!=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where convert(varchar(8),left(序号,8),112) between convert(varchar(8),cast('''+@startday+''' as datetime),112)
and convert(varchar(8),cast('''+@endday+''' as datetime),112)'
else
if @orderid!=null and @startday=null
select @sql =left(@sql,len(@sql)-1) + ' from v_order_workstage_count group by OrderId,id) as p1
where 订单号='''+ @orderid +''''
select @sql=@sql+'select distinct #a.*,engType as 机型
from #a left JOIN dbo.tbl_planOrder ON dbo.tbl_planOrder.orderid = #a.订单号
union all
select null,''汇总'',sum([ 订单数量]),sum([1030]),sum([1450]),sum([3010]),sum([3310]),sum([4300]),sum([6140]),null from #a
order by 序号 desc
drop table #a'
exec(@sql)
GO
优化存储过程SQL
tbl_Record表中有有36万条记录
tbl_definetimerecord是根据tbl_Record表创建的视图。
Sql SERVER 版本是2000。
第一个存储过程运行时间大概7秒
第二个存储过程运行时间大概5秒。
ctrl+l,然后执行exec a 参数(如果没有参数就不用加了,直接exec a)
视图的代码:SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER VIEW [dbo].[tbl_DefineTimeRecord] with SCHEMABINDING AS
SELECT
dbo.tbl_Record.NoId,
dbo.tbl_Record.Workstage,
dbo.tbl_Record.EngId,
dbo.Convert_Date(dbo.tbl_Record.Time) AS RecordTime,
dbo.tbl_PlanOrder.EngTypeFROM
dbo.tbl_Order
INNER JOIN dbo.tbl_PlanOrder ON dbo.tbl_PlanOrder.OrderId = dbo.tbl_Order.OrderId
RIGHT JOIN dbo.tbl_Record ON dbo.tbl_Order.EngId = dbo.tbl_Record.EngId
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GO
SET ANSI_NULLS ON
GO/*计算小时平均产量*/
ALTER PROCEDURE [dbo].[udt_avghourcount]
@startdate datetime,@enddate datetime
AS
Set Nocount On
/*@days 两日期之间有产量的天数*/
declare @days int
/*加了以下这段代码*/
select * into #tmp from(SELECT top 100 percent NoId, Workstage, EngId, dbo.Convert_Date([Time]) AS RecordTime
FROM dbo.tbl_Record
where [time] between dateadd(d,-1,@startdate) and dateadd(d,1,@enddate)
ORDER BY [Time] DESC) as p2
/*加了以上这段代码*/
select @days=count([date]) from(
select DISTINCT convert(varchar(10),recordtime,23) as [date]
from #tmp /*tbl_definetimerecord*/
where recordtime between @startdate and dateadd(d,1,@enddate)) as p0 select * into #a from (select top 100 percent workstage,right(time,2) as [hour],cast(sum(total)*1.0/(@days*1.0)
as decimal(5,2)) as averagecount
from (select workstage,convert(varchar(13),recordtime,20) as [time],
count(engid)*1.0 as total
from #tmp /*tbl_definetimerecord*/
where recordtime between @startdate and dateadd(d,1,@enddate)
group by convert(varchar(13),recordtime,20) ,workstage) as p1
group by workstage,right(time,2)
order by workstage) as p2 declare @sql varchar(8000)
set @sql='select case workstage
when ''1030'' then ''内装上线''
when ''1450'' then ''内装下线''
when ''3010'' then ''外装上线''
when ''3310'' then ''外装下线''
when ''4300'' then ''预装下线''
when ''6040'' then ''装风扇''
when ''6140'' then ''包装''
else workstage end as 工序,'
set @sql=@sql+'sum(case [hour] when ''00'' then averagecount else 0 end) as ''08:30'','
set @sql=@sql+'sum(case [hour] when ''01'' then averagecount else 0 end) as ''09:30'','
set @sql=@sql+'sum(case [hour] when ''02'' then averagecount else 0 end) as ''10:30'','
set @sql=@sql+'sum(case [hour] when ''03'' then averagecount else 0 end) as ''11:30'','
set @sql=@sql+'sum(case [hour] when ''04'' then averagecount else 0 end) as ''12:30'','
set @sql=@sql+'sum(case [hour] when ''05'' then averagecount else 0 end) as ''13:30'','
set @sql=@sql+'sum(case [hour] when ''06'' then averagecount else 0 end) as ''14:30'','
set @sql=@sql+'sum(case [hour] when ''07'' then averagecount else 0 end) as ''15:30'','
set @sql=@sql+'sum(case [hour] when ''08'' then averagecount else 0 end) as ''16:30'','
set @sql=@sql+'sum(case [hour] when ''09'' then averagecount else 0 end) as ''17:30'','
set @sql=@sql+'sum(case [hour] when ''10'' then averagecount else 0 end) as ''18:30'','
set @sql=@sql+'sum(case [hour] when ''11'' then averagecount else 0 end) as ''19:30'','
set @sql=@sql+'sum(case [hour] when ''12'' then averagecount else 0 end) as ''20:30'','
set @sql=@sql+'sum(case [hour] when ''13'' then averagecount else 0 end) as ''21:30'','
set @sql=@sql+'sum(case [hour] when ''14'' then averagecount else 0 end) as ''22:30'','
set @sql=@sql+'sum(case [hour] when ''15'' then averagecount else 0 end) as ''23:30'','
set @sql=@sql+'sum(case [hour] when ''16'' then averagecount else 0 end) as ''00:30'','
set @sql=@sql+'sum(case [hour] when ''17'' then averagecount else 0 end) as ''01:30'','
set @sql=@sql+'sum(case [hour] when ''18'' then averagecount else 0 end) as ''02:30'','
set @sql=@sql+'sum(case [hour] when ''19'' then averagecount else 0 end) as ''03:30'','
set @sql=@sql+'sum(case [hour] when ''20'' then averagecount else 0 end) as ''04:30'','
set @sql=@sql+'sum(case [hour] when ''21'' then averagecount else 0 end) as ''05:30'','
set @sql=@sql+'sum(case [hour] when ''22'' then averagecount else 0 end) as ''06:30'','
set @sql=@sql+'sum(case [hour] when ''23'' then averagecount else 0 end) as ''07:30'','
set @sql=left(@sql,len(@sql)-1)+ 'from #a group by workstage'
select @sql= 'select *,([08:30]+[09:30]+[10:30]+[11:30]+[12:30]+[13:30]+[14:30]+[15:30]
+[16:30]+[17:30]+[18:30]+[19:30]+[20:30]+[21:30]+[22:30]+[23:30]+[00:30]+[01:30]+[02:30]
+[03:30]+[04:30]+[05:30]+[06:30]+[07:30]) as 汇总 from ('+@sql+') as p3'
exec(@sql)
--drop table #tmp
drop table #a
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO