写了一个存储过程，在主机上执行的结果，当数据行为5行时，只有100ms,当数据行为30行时，达到了1000-1600ms,查找后发现问题出在代码的最后一段，它是以行为单位，统计本行前面所有行的和（其实是手工做帐时方便检查的，系统上可以不用的，但老板一定要做上去），有点像游标的东西，但没有用游标实现，代码如下
declare @PNE varchar(64),@PID int;
set @PNE='钢管'; set @pid=1;
with t as (
select Row_Number() OVER ( ORDER by orderdate ASC) rank, a.ID,a.ProjectID,a.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate,sum(a.SubTotal) as SubTotal from (
select a.ID,a.ProjectID,b.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate,a.SubTotal from (
select a.ID,b.ProductName,a.ProjectID,a.OrderDirection,a.OrderDate,a.SubTotal from (
select a.ID,a.ProjectID,b.StandardID,a.OrderDirection,a.OrderDate,b.SubTotal from SteelBusinessOrder a
join (select StandardID,sum(SubTotal) as SubTotal,OrderID from SteelBusinessList group by StandardID,OrderID) b
on a.ID = b.OrderID
) a
join SteelStandard b
on a.StandardID = b.ID and b.ProductName = @PNE
) a
join SteelProject b
on a.ProjectID = b.ID and b.ID = @PID
) a group by a.ID,a.ProjectID,a.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate
),ts as (
select *,uday * surplus as counts from (
select *,
(select DATEDIFF(d,(select OrderDate from t a where a.rank = b.rank)
,case when((select min(OrderDate) from t a where a.rank > b.rank ) is null) then dateadd(d,1,getdate())
else (select min(OrderDate) from t a where a.rank>b.rank ) end)) as uday
,
isnull(
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 0 ),0)
-
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 1 ),0)
,0)
as surplus
from t as b
) a
)
select convert(char(10),orderdate,120) as orderdate,productName,(case orderdirection when 0 then '出库' when 1 then '入库' end) as orderdirection,subtotal,surplus,uday,counts
,(select SUM(counts) as zsum from ts a where a.rank <= b.rank) as zsum
from ts b
--create index ProductName_index on SteelBusinessOrder (ProductName)
求指导应该怎么修改呢？
------解决方案--------------------
用法不同，效果自然不一样。ORACLE有累加指令，而且性能OK，SQL SERVER要变通实现