聚合查询，求优化方案！速度太慢了

[四更山吐月]

数据表字段：规格spec、品牌brandName、品号materialName、标准重量standardweight、实际重量weight，检测时间XCheckTime
需要查询出来如下信息：
序号、规格、品牌、品号、标准重量、早班数量、中班数量、夜班数量、日最大、日最小、日极差、日平均、日重量百分比、周最大重量、周最小重量、周极差、当周平均值、周重量百分比、月最大重量、月最小重量、月极差、当月平均值、月重量百分比

并且按照品号分组，帮忙看看，查询结果能出来，就是效率太低。。有没有什么优化方案。请赐教！
日最大、日最小能通过max和min直接做，日极差就是日最大-日最小。
我存储过程：
USE [WeightCheck]
GO
/****** 对象: StoredProcedure [dbo].[sp_dayReport] 脚本日期: 10/21/2012 07:40:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_dayReport]
@day datetime
AS
declare @t table(
id int,
spec varchar(100),
materialName varchar(50),
brandName varchar(50),
standardWeight decimal(18,2),
counter int,
shiftCount1 int,
shiftCount2 int,
shiftCount3 int,
maxDay decimal(18,2),
minDay decimal(18,2),
diffDay decimal(18,2),
avgDay decimal(18,2),
percentDay varchar(50),
maxWeek decimal(18,2),
minWeek decimal(18,2),
diffWeek decimal(18,2),
avgWeek decimal(18,2),
percentWeek varchar(50),
maxMonth decimal(18,2),
minMonth decimal(18,2),
diffMonth decimal(18,2),
avgMonth decimal(18,2),
percentMonth varchar(50)
)
declare
@id int,
@spec varchar(100),
@materialName varchar(50),
@brandName varchar(50),
@standardWeight decimal(18,2),
@counter int,
@shiftCount1 int,
@shiftCount2 int,
@shiftCount3 int,
@maxDay decimal(18,2),
@minDay decimal(18,2),
@diffDay decimal(18,2),
@avgDay decimal(18,2),
@percentDay varchar(50),
@maxWeek decimal(18,2),
@minWeek decimal(18,2),
@diffWeek decimal(18,2),
@avgWeek decimal(18,2),
@percentWeek varchar(50),
@maxMonth decimal(18,2),
@minMonth decimal(18,2),
@diffMonth decimal(18,2),
@avgMonth decimal(18,2),
@percentMonth varchar(50)
BEGIN
SET NOCOUNT ON;
set @id=0;
declare myCursor Cursor for

select materialName,(sizeName+' '+PlyRatingName+' '+PatternName) as spec,brandName,
standardWeight,
(select count(materialName) from v_xrecord d1 where datediff(day,@day,XCheckTime)=0 and d1.materialName=v.materialName) as counter,
(select count(materialName) from v_xrecord s1 where datediff(day,@day,XCheckTime)=0 and s1.DayShiftNo='01' and s1.materialName=v.materialName) as shiftCount1,
(select count(materialName) from v_xrecord s2 where datediff(day,@day,XCheckTime)=0 and s2.DayShiftNo='02' and s2.materialName=v.materialName) as shiftCount2,
(select count(materialName) from v_xrecord s3 where datediff(day,@day,XCheckTime)=0 and s3.DayShiftNo='03' and s3.materialName=v.materialName) as shiftCount3,
(select max(weight) from v_xrecord d2 where datediff(day,@day,XCheckTime)=0 and d2.materialName=v.materialName) as maxDay,
(select min(weight) from v_xrecord d3 where datediff(day,@day,XCheckTime)=0 and d3.materialName=v.materialName) as minDay,
(select avg(weight) from v_xrecord t1 where datediff(day,@day,XCheckTime)=0 and t1.materialName=v.materialName) as avgDay,
(select max(weight) from v_xrecord t2 where datepart(week,@day)=datepart(week,XCheckTime) and t2.materialName=v.materialName) as maxWeek,
(select min(weight) from v_xrecord t3 where datepart(week,@day)=datepart(week,XCheckTime) and t3.materialName=v.materialName) as minWeek,
(select avg(weight) from v_xrecord t4 where datepart(week,@day)=datepart(week,XCheckTime) and t4.materialName=v.materialName) as avgWeek,
(select max(weight) from v_xrecord t5 where datediff(month,@day,XCheckTime)=0 and t5.materialName=v.materialName) as maxMonth,
(select min(weight) from v_xrecord t6 where datediff(month,@day,XCheckTime)=0 and t6.materialName=v.materialName) as minMonth,
(select avg(weight) from v_xrecord t7 where datediff(month,@day,XCheckTime)=0 and t7.materialName=v.materialName) as avgMonth
from v_xrecord v where datediff(day, XCheckTime,@day)=0
group by sizeName,PlyRatingName,PatternName,brandName,materialName,
standardWeight,

open myCursor
fetch next from myCursor into @spec,@materialName,@brandName,
@standardWeight,
@counter,@shiftCount1,@shiftCount2,@shiftCount3,
@maxDay ,@minDay ,@avgDay,
@maxWeek,@minWeek,@avgWeek,@maxMonth,@minMonth,@avgMonth
while(@@fetch_status=0)
begin
set @id=@id+1;
set @diffDay= @maxDay-@minDay;
--percent= (avg-standard)/standard
set @percentDay='0%';
if (@standardWeight<>0)
begin
set @percentDay=cast(CONVERT(DECIMAL(18,2),(@avgDay-@standardWeight)/@standardWeight*100) as varchar(8))+'%';
end
set @diffWeek= @maxWeek-@minWeek;
set @percentWeek='0%';
if (@standardWeight<>0)
begin
set @percentWeek=cast(CONVERT(DECIMAL(18,2),(@avgWeek-@standardWeight)/@standardWeight*100) as varchar(8))+'%';
end
set @diffMonth= @maxMonth-@minMonth;
set @percentMonth='0%';
if (@standardWeight<>0)
begin
set @percentMonth=cast(CONVERT(DECIMAL(18,2),(@avgMonth-@standardWeight)/@standardWeight*100) as varchar(8))+'%';
end
insert into @t(id,spec ,materialName ,brandName,
standardWeight, counter,shiftCount1,shiftCount2,shiftCount3,maxDay,minDay,diffDay,avgDay,percentDay,maxWeek,minWeek,diffWeek,avgWeek,
percentWeek,maxMonth,minMonth,diffMonth,avgMonth,percentMonth)
values (
@id,@spec ,@materialName ,@brandName,@u30Weight,@u20Weight,@u15Weight,@u10Weight,
@u05Weight,@standardWeight,@d05Weight,@d10Weight,@d15Weight,@d20Weight,@d30Weight,
@counter,@shiftCount1,@shiftCount2,@shiftCount3,@maxDay,@minDay,@diffDay,@avgDay,
@percentDay,@maxWeek,@minWeek,@diffWeek,@avgWeek,
@percentWeek,@maxMonth,@minMonth,@diffMonth,@avgMonth,@percentMonth
)

fetch next from myCursor into @spec,@materialName,@brandName,
@standardWeight
@counter,@shiftCount1,@shiftCount2,@shiftCount3,@maxDay ,@minDay ,@avgDay,
@maxWeek,@minWeek,@avgWeek,@maxMonth,@minMonth,@avgMonth
end
close myCursor;
deallocate myCursor;
select * from @t;

END

[DBA_磊仔]

写法缺乏集合的思想，局部变量太多

[DBA_磊仔]

declare myCursor Cursor for
select materialName,(sizeName+' '+PlyRatingName+' '+PatternName) as spec,brandName,
standardWeight,count(*) as counter,  
sum(case DayShiftNo when '01' then 1 else 0 end) as shiftCount1,
sum(case DayShiftNo when '02' then 1 else 0 end) as shiftCount2,
sum(case DayShiftNo when '03' then 1 else 0 end) as shiftCount3,
max(weight)maxDay,min(weight)minDay,avg(weight)avgDay,
max(case when datepart(week,@day)=datepart(week,XCheckTime) then weight else null end)maxWeek,
min(case when datepart(week,@day)=datepart(week,XCheckTime) then weight else null end)minWeek,
avg(case when datepart(week,@day)=datepart(week,XCheckTime) then weight else null end)avgWeek,
max(case when datediff(month,@day,XCheckTime)=0 then weight else null end)maxMonth,
min(case when datediff(month,@day,XCheckTime)=0 then weight else null end)minMonth,
avg(case when datediff(month,@day,XCheckTime)=0 then weight else null end)avgMonth
from v_xrecord v where datediff(day, XCheckTime,@day)=0  
group by sizeName,PlyRatingName,PatternName,brandName,materialName,
standardWeight 

[DBA_磊仔]

我看你的实现，应该是可以做到不用游标的，建议思考一下集合的实现方法

[四更山吐月]

顶呀，没人在吗，

[专注or全面]

我不知道你这个是不是视图，v_xrecord
你在查询条件上用到了函数，有索引也用不上了
用游标本身倒是没什么问题

[专注or全面]

类似于这种统计，
建议写一个job定期的跑 ，把数据统计出来放到一个表中，需要的时候直接取表中取
即时统计出来是很慢的（数据量大的时候），