这段程序怎么优化，请高手帮忙

[cn_afei]

环境：Sql Server 2000
目的：得到正品、废次品各自的实际重量因为可能有退库的可能所以要减掉退库的量，怎样优化一下，因为我要循环调用此代码，我再查询分析器中执行此代码需要1秒，假如循环的话会很慢的。其中Z_Pt_Weight表中有大约5万条记录并且一直增加。请高手帮忙优化一下，在此先谢谢了。代码如下
--得到所有的正品重（可能包括退库）
SELECT @GoodsWeightTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '0'

--得到所有的次品重（可能包括退库）
SELECT @SmeltCiTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '1' AND JudgeDuty = '0'

--得到所有的次品重（可能包括退库）
SELECT @RollingCiTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '1' AND JudgeDuty = '1'

--得到所有的次品重（可能包括退库）
SELECT @CheckCiTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '1' AND JudgeDuty = '2'

--得到所有的废品重（可能包括退库）
SELECT @SmeltFeiTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '2' AND JudgeDuty = '0'

--得到所有的废品重（可能包括退库）
SELECT @RollingFeiTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '2' AND JudgeDuty = '1'

--得到所有的废品重（可能包括退库）
SELECT @CheckFeiTotal = SUM ( ISNULL ( Weight_Net , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND JudgeResult = '2' AND JudgeDuty = '2'

--得到正品重（包括退库所有的正品重 - 所有的退库正品重 ）
SELECT @GoddsWeight = @GoodsWeightTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '0'

--得到次品重（包括退库所有的次品重 - 所有的退库次品重 ）
SELECT @SmeltCi = @SmeltCiTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '1' AND JudgeDuty = '0'

--得到次品重（包括退库所有的次品重 - 所有的退库次品重 ）
SELECT @RollingCi = @RollingCiTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '1' AND JudgeDuty = '1'

--得到次品重（包括退库所有的次品重 - 所有的退库次品重 ）
SELECT @CheckCi = @CheckCiTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '1' AND JudgeDuty = '2'

--得到废品重（包括退库所有的废品重 - 所有的退库废品重 ）
SELECT @SmeltFei = @SmeltFeiTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '2' AND JudgeDuty = '0'

--得到废品重（包括退库所有的废品重 - 所有的退库废品重 ）
SELECT @RollingFei = @RollingFeiTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '2' AND JudgeDuty = '1'

--得到废品重（包括退库所有的废品重 - 所有的退库废品重 ）
SELECT @CheckFei = @CheckFeiTotal - SUM ( ISNULL ( Weight_Gross , 0 ) )
FROM Z_Pt_Weight
WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') AND Heat_id <> @HeatId AND JudgeResult = '2' AND JudgeDuty = '2'
INSERT QualityArtStaticsJudgeDate ( Cp6_7Date , HeatId , GoodsWeight , SmeltCi , RollingCi , CheckCi , SmeltFei , RollingFei , CheckFei )
VALUES(@Cp6_7Date , @HeatId , @GoddsWeight , @SmeltCi , @RollingCi , @CheckCi , @SmeltFei , @RollingFei , @CheckFei)

[cn_afei]

谢了，再问一下
1、在存贮过程中调用存贮过程和写到一个存贮过程中那一种速度快？
2、在Sql Server 2000中有没有设置参数对存贮过程的速度进行优化的？

[chenun]

你这段语句反复地扫描一张大表，并且还要循环执行，这样性能不可能很高，
可以考虑这样的思路：
尽可能在一次表扫描中完成所有的计算，改为下面的语句：
INSERT QualityArtStaticsJudgeDate ( Cp6_7Date , HeatId , GoodsWeight ,
SmeltCi , RollingCi , CheckCi , SmeltFei , RollingFei , CheckFei )
select @Cp6_7Date , @HeatId, SUM ( case when JudgeResult = '0' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '0' then ISNULL ( Weight_Gross , 0 ) else 0 end ),
SUM ( case when JudgeResult = '1' AND JudgeDuty = '0' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '1' AND JudgeDuty = '0' then ISNULL ( Weight_Gross , 0 ) else 0 end ),
SUM ( case when JudgeResult = '1' AND JudgeDuty = '1' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '1' AND JudgeDuty = '1' then ISNULL ( Weight_Gross , 0 ) else 0 end ),
SUM ( case when JudgeResult = '1' AND JudgeDuty = '2' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '1' AND JudgeDuty = '2' then ISNULL ( Weight_Gross , 0 ) else 0 end ),
SUM ( case when JudgeResult = '2' AND JudgeDuty = '0' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '2' AND JudgeDuty = '0' then ISNULL ( Weight_Gross , 0 ) else 0 end ),
SUM ( case when JudgeResult = '2' AND JudgeDuty = '1' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '2' AND JudgeDuty = '1' then ISNULL ( Weight_Gross , 0 ) else 0 end ),
SUM ( case when JudgeResult = '2' AND JudgeDuty = '2' then ISNULL ( Weight_Net , 0 ) else 0 end ) -
SUM ( case when Heat_id <> @HeatId AND JudgeResult = '2' AND JudgeDuty = '2' then ISNULL ( Weight_Gross , 0 ) else 0 end )
from Z_Pt_Weight
where Heat_Id LIKE (LEFT(@HeatId , 10) + '%')
如果为取得最佳的性能，应该在一个批处理中完成所有需要的计算。按需求确定好分组条件，上面的select语句稍加改动就可以一次生成所有最终结果。

[cn_afei]

上面的代码是从一个表中取的数据假如我要从不同的表中取出部分数据然后放到一个表中有什么优化速度的方法
1、如果用视图的话由于
（1）主键不同连接后记录量会增大同时增加查询时间
（2）由于一些字段要用到聚合、另一些要取唯一值、还有一些要取其中不同值对应的不同值（例如：取生产同一炉下的不同的班别、同时计算同相应的产量）所以不能同时得到
2、如果用一个中间表每天再服务器上统计一次、但是当重新统计时速度还是会很慢的。
请各位高手给一些意见！！

[zqllyh]

1、用CASE把几个SELECT 合在一起吧、
比如@GoodsWeightTotal = SUM ( ISNULL ( (case dd when 0 then Weight_Net else .... end), 0 ) )

2、(LEFT(@HeatId , 10) + '%') 用一个变量去代替
3、把Z_Pt_Weight WHERE Heat_Id LIKE (LEFT(@HeatId , 10) + '%') 先做成一个视图。

[cn_afei]

我这里有两个条件JudgeResult和JudgeDuty来分别判断但是不知道在case语句中怎么写case语句，能否给个示范

[zqllyh]

select @GoodsWeightTotal = SUM ( ISNULL ( (case dd when 0 then Weight_Net else .... end), 0 ) )
,第二个值的赋值。。。。。 from table where 条件。。。