新人求助 存储过程执行时间长

[aluzer1003]

下面代码：本来执行时间在2秒内，现在要2分钟了，用的是服务器（R1） 数据约50000条
CREATE procedure 库存查询
@XZ smalldatetime
AS
BEGIN
SELECT @XZ AS 查询日期, A.产品批号 AS 产品批号, A.产品规格 AS 产品规格, A.原料机型 AS 原料机型, A.产品色别 AS 产品色别, A.筒管颜色 AS 筒管颜色,
CAST(ISNULL((SELECT COUNT(B.装箱编号) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.装箱日期<@XZ),0.00)-
ISNULL((SELECT COUNT(B.装箱编号) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.发货日期<@XZ),0.00) AS decimal(18,0)) AS 昨存件数,
CAST(ISNULL((SELECT SUM(B.产品净重) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.装箱日期<@XZ),0.00)-
ISNULL((SELECT SUM(B.产品净重) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.发货日期<@XZ),0.00) AS decimal(18,2)) AS 昨存重量,
CAST(ISNULL((SELECT COUNT(B.装箱编号) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.装箱日期=@XZ),0.00) AS decimal(18,0)) AS 今增件数,
CAST(ISNULL((SELECT SUM(B.产品净重) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.装箱日期=@XZ),0.00) AS decimal(18,2)) AS 今增重量,
CAST(ISNULL((SELECT COUNT(B.装箱编号) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.发货日期=@XZ),0.00) AS decimal(18,0)) AS 今发件数,
CAST(ISNULL((SELECT SUM(B.产品净重) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.发货日期=@XZ),0.00) AS decimal(18,2)) AS 今发重量,
CAST(ISNULL((SELECT COUNT(B.装箱编号) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.装箱日期<=@XZ),0.00)-
ISNULL((SELECT COUNT(B.装箱编号) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.发货日期<=@XZ),0.00) AS decimal(18,0)) AS 今存件数,
CAST(ISNULL((SELECT SUM(B.产品净重) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.装箱日期<=@XZ),0.00)-
ISNULL((SELECT SUM(B.产品净重) FROM dbo.条码装箱 AS B WHERE A.产品批号 = B.产品批号 AND A. 筒管颜色 = B.筒管颜色 AND B.发货日期<=@XZ) ,0.00) AS decimal(18,2)) AS 今存重量,
A.机台编号 AS 机台编号, A.仓库编号 AS 仓库编号
FROM dbo.条码装箱 A JOIN dbo.条码装箱 B ON A.产品批号 =B.产品批号 AND A. 筒管颜色 = B.筒管颜色 WHERE @XZ=@XZ
GROUP BY A.产品批号,A.产品规格, A.原料机型, A.产品色别, A.筒管颜色, A.机台编号, A.仓库编号
END
GO
哪里需要改进

[中国风]

加在 AS 后面 如 CREATE PROCEDURE ... AS SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

[aluzer1003]

同样数据在PC机执行时间也是2秒，第一次写存储过程，有没有少语句？

[aluzer1003]

这是一个根据日期来查看库存，装箱日期就是生产日期 ，发货日期就是出库日期，要给一个日期 显示这个日期前的库存，这个时间的生产数量，发货数量，这个日期的库存

[二月十六]

把哪些子查询去掉，下边按照子查询的条件做表连接，因为有许多条件相同的子查询，只不过是取得表的值不一样。

[二月十六]

类似这种，你看看可以不？

SELECT  @XZ AS 查询日期 ,
                CAST(ISNULL(( COUNT(B.装箱编号) ), 0.00) - ISNULL(( COUNT(C.装箱编号) ),
                                                              0.00) AS DECIMAL(18,
                                                              0)) AS 昨存件数
        FROM    dbo.条码装箱 A
                JOIN dbo.条码装箱 B ON A.产品批号 = B.产品批号
                                   AND A.筒管颜色 = B.筒管颜色
                                   AND B.装箱日期 < @XZ
                JOIN dbo.条码装箱 C ON A.产品批号 = C.产品批号
                                   AND A.筒管颜色 = C.筒管颜色
                                   AND B.发货日期 < @XZ

[aluzer1003]

还是不行，R1有影响吗

[Tiger_Zhao]

    SELECT @XZ AS 查询日期,
           a.产品批号 AS 产品批号,
           a.产品规格 AS 产品规格,
           a.原料机型 AS 原料机型,
           a.产品色别 AS 产品色别,
           a.筒管颜色 AS 筒管颜色,
           ISNULL(COUNT(b1.装箱编号),0) AS 昨存件数,
           ISNULL(SUM(b1.产品净重),0.00) AS 昨存重量,
           ISNULL(COUNT(b2.装箱编号),0) AS 今增件数,
           ISNULL(SUM(b2.产品净重),0.00) AS 今增重量,
           ISNULL(COUNT(b3.装箱编号),0) AS 今发件数,
           ISNULL(SUM(b3.产品净重),0.00) AS 今发重量,
           ISNULL(COUNT(b4.装箱编号),0) AS 今存件数,
           ISNULL(SUM(b4.产品净重),0.00) AS 今存重量,
           a.机台编号 AS 机台编号,
           a.仓库编号 AS 仓库编号
      FROM dbo.条码装箱 a
 LEFT JOIN dbo.条码装箱 b1
        ON a.产品批号 = b1.产品批号
       AND a.筒管颜色 = b1.筒管颜色
       AND b1.装箱日期 < @XZ
       AND b1.发货日期 < @XZ
 LEFT JOIN dbo.条码装箱 b2
        ON a.产品批号 = b2.产品批号
       AND a.筒管颜色 = b2.筒管颜色
       AND b2.装箱日期 = @XZ
 LEFT JOIN dbo.条码装箱 b3
        ON a.产品批号 = b3.产品批号
       AND a.筒管颜色 = b3.筒管颜色
       AND b3.发货日期 = @XZ
 LEFT JOIN dbo.条码装箱 b4
        ON a.产品批号 = b4.产品批号
       AND a.筒管颜色 = b4.筒管颜色
       AND b4.装箱日期 < @XZ
       AND b4.发货日期 = @XZ
 GROUP BY a.产品批号,a.产品规格,a.原料机型,a.产品色别,a.筒管颜色,a.机台编号,a.仓库编号

条码装箱表需要以下索引
(产品批号,筒管颜色,装箱日期,发货日期)包含(装箱编号,产品净重)
(产品批号,筒管颜色,发货日期)包含(装箱编号,产品净重)

[中国风]

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

在语句前加上脏读试试 查询语所用到的对象在其它事务正在变更时没提交会阻塞

[aluzer1003]

引用 7 楼 roy_88 的回复:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

在语句前加上脏读试试 查询语所用到的对象在其它事务正在变更时没提交会阻塞

加在那里后面 BEGIN后？

[aluzer1003]

谢谢大家的回复，同样的过程放在PC或一个硬盘的服务器速度都OK 现在怀疑是R1 或服务器配置有问题？

[吉普赛的歌]

加上 with recompile 试下， 99%可能是这个原因！

CREATE procedure 库存查询
@XZ smalldatetime

WITH RECOMPILE
AS
BEGIN
...
end

[sean-yuan]

多条 ISNULL（子查询，）能不慢么，建议 left join 一下 会快很多。