首页新闻找找看学习计划

请教SQL优化

0
悬赏园豆:20 [已解决问题] 解决于 2013-05-22 14:49

我执行了一个存储过程,返回5行时间在25ms,返回29数据的反应速度达到1秒了,如下,机器内存4G,使用3.3G,应该如何解决呢?

 

SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

(29 行受影响)

SQL Server 执行时间:
   CPU 时间 = 1014 毫秒,占用时间 = 1008 毫秒。

SQL Server 执行时间:
   CPU 时间 = 1014 毫秒,占用时间 = 1008 毫秒。
SQL Server 分析和编译时间:
   CPU 时间 = 0 毫秒,占用时间 = 0 毫秒

问题补充:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Expensedetails]
        @PID int,
        @PNE nvarchar(64)
as
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
happydaily的主页 happydaily | 小虾三级 | 园豆:648
提问于:2013-05-20 18:28
< >
分享
最佳答案
0

ProductName,rank 有没有做索引?

收获园豆:10
Yu | 专家六级 |园豆:12944 | 2013-05-20 22:17

问题找到,就是 最后一句(select SUM(counts) as zsum from ts a where a.rank < = b.rank) as zsum 影响比较大,去掉这个只有100ms,加上这个达到1000ms,应该怎么办呢

happydaily | 园豆:648 (小虾三级) | 2013-05-21 08:54

ProductName不需要做索引吧?目前只有三种可能;Rank是由Orderdate演变来的,已经做了索引,测试对结果没有影响。

happydaily | 园豆:648 (小虾三级) | 2013-05-21 09:09

@happydaily: 

对 

SubTotal 和 orderdirection  作索引试试吧
Yu | 园豆:12944 (专家六级) | 2013-05-21 12:38
其他回答(3)
0

存储过程呢????

jerry-Tom | 园豆:4077 (老鸟四级) | 2013-05-20 18:36
0

想办法用a.rank <  b.rank 代替 a.rank < = b.rank,如:

(select SUM(counts) as zsum from ts a where a.rank < (b.rank+1)) as zsum

收获园豆:5
settan | 园豆:154 (初学一级) | 2013-05-21 09:07

经测试,两者效果一样

支持(0) 反对(0) happydaily | 园豆:648 (小虾三级) | 2013-05-21 09:11
0

你可以考虑考虑 联查

用 inner join 

可能比单个表中要快,把过滤效果明显的条件 尽量往前放

收获园豆:5
二十三号同学 | 园豆:941 (小虾三级) | 2013-05-22 11:10
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册