Geek Answers Handbook

SQL Server: Create an optimized view that retrieves the most recent nonzero value

I made this explicitly not optimized view in SQL Server:

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

The purpose of this view is to retrieve from the T_DETAIL table the last value of NOT NULL (or NOT 0 in some cases).

Example: 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    |
+-----+------+------+----------------+-------+-------+-------+

in the view is as follows:

+-----+------+------+----------------+-------+-------+-------+
| 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    |
+-----+------+------+----------------+-------+-------+-------+

The view works, but very slow. Where should I optimize it? I tried to make this an indexed view, but SQL Server Manager warned me that it was ORDER BYonly used TOP 1to retrieve the most recent value. I think I should start with this, but how? Maybe using MAX()somewhere would be a better choice, but I don't want to add complexity and joke.

? , , ?

+4
2

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 .

+1

"

datetime ( ) datetime?

, , datetime ( ) " ", A B Z .

0

More articles:


All Articles