SQL Server : Create an optimized view that retrieve the most recent non null value

Question

我已经在 SQL Server 中完成了这显然没有优化的 View :

SELECT     ID, T_ID, SRNB, P_DATETIME,
                      (SELECT     TOP (1) COL_A
                        FROM          dbo.T_DETAIL AS T
                        WHERE      (T_ID = a.T_ID) AND (COL_A IS NOT NULL) AND (P_DATETIME <= a.P_DATETIME)
                        ORDER BY P_DATETIME DESC) AS COL_A, COL_A_MU,
                      (SELECT     TOP (1) COL_B
                        FROM          dbo.T_DETAIL AS T
                        WHERE      (T_ID = a.T_ID) AND (COL_B IS NOT NULL) AND (P_DATETIME <= a.P_DATETIME)
                        ORDER BY P_DATETIME DESC) AS COL_B, COL_B_MU,
--...for several columns
                      (SELECT     TOP (1) COL_Z
                        FROM          dbo.T_DETAIL AS T
                        WHERE      (T_ID = a.T_ID) AND (COL_Z > 0) AND (P_DATETIME <= a.P_DATETIME)
                        ORDER BY P_DATETIME DESC) AS COL_Z
FROM         dbo.T_DETAIL AS a

此 View 的目的是从表 T_DETAIL 中获取最新的 NOT NULL(或在某些情况下为 NOT 0)值。

示例:T_DETAIL 是

+-----+------+------+----------------+-------+-------+-------+
| ID  | T_ID | SRNB |   P_DATETIME   | COL_A | COL_B | COL_Z |
+-----+------+------+----------------+-------+-------+-------+
| xxx | aaa  | aaa  | 20131205 20:15 | 5     | NULL  | 10    |
| xxx | aaa  | aaa  | 20131205 20:16 | NULL  | 10    | NULL  |
| xxx | aaa  | aaa  | 20131205 20:17 | NULL  | 5     | 5     |
| xxx | aaa  | aaa  | 20131205 20:18 | 5     | NULL  | NULL  |
| xxx | aaa  | aaa  | 20131205 20:19 | NULL  | NULL  | 11    |
| xxx | aaa  | aaa  | 20131205 20:20 | 7     | NULL  | 10    |
+-----+------+------+----------------+-------+-------+-------+

在 View 中变成这样:

+-----+------+------+----------------+-------+-------+-------+
| ID  | T_ID | SRNB |   P_DATETIME   | COL_A | COL_B | COL_Z |
+-----+------+------+----------------+-------+-------+-------+
| xxx | aaa  | aaa  | 20131205 20:15 | 5     | NULL  | 10    |
| xxx | aaa  | aaa  | 20131205 20:16 | 5     | 10    | 10    |
| xxx | aaa  | aaa  | 20131205 20:17 | 5     | 5     | 5     |
| xxx | aaa  | aaa  | 20131205 20:18 | 5     | 5     | 5     |
| xxx | aaa  | aaa  | 20131205 20:19 | 5     | 5     | 11    |
| xxx | aaa  | aaa  | 20131205 20:20 | 7     | 5     | 10    |
+-----+------+------+----------------+-------+-------+-------+

该 View 有效，但速度非常缓慢。我应该从哪里开始优化它？我试图使它成为索引 View ，但 SQL Server 管理器警告我 ORDER BY仅用于 TOP 1用于检索最近的值。我想我应该从那开始，但是如何呢？也许使用 MAX()某处将是一个更好的选择，但我不想增加复杂性并把事情搞砸。

我应该走哪条路？也许有一种规范的方式来实现我正在寻找的东西？

Answer 1

每个COL_X子查询，而不是:

(SELECT     TOP (1) COL_A
 FROM          dbo.T_DETAIL AS T
 WHERE      (T_ID = a.T_ID) AND (COL_A IS NOT NULL) AND (P_DATETIME <= a.P_DATETIME)
 ORDER BY P_DATETIME DESC) AS COL_A, COL_A_MU

尝试这个:

  (SELECT     COL_A
   FROM       dbo.T_DETAIL AS T
   WHERE      T_ID = a.T_ID
   AND        P_DATETIME =    
     (select  max(P_DATETIME) 
      FROM    dbo.T_DETAIL AS T2
      WHERE   T_ID = a.T_ID AND COL_A IS NOT NULL
      AND     P_DATETIME <= a.P_DATETIME
     )
   ) AS COL_A, 

我无法测试它，但我希望这会有所帮助。

为了获得更好的性能，在 T_ID 上建立索引可能会有所帮助。 (以及其他键列，如果它们在连接中使用)和 P_DATETIME在同一个索引中。