Performance improvement Outer Apply

I have a query which runs slower.

SELECT * 
FROM [Obe].[dbo].[vFan] P 
OUTER APPLY
    (SELECT TOP 1 [CT_XY] 
     FROM [Obe].[dbo].[vFan] 
     WHERE [row_num] <= P.[row_num] 
       AND [CT_XY] IS NOT NULL 
       AND [CT_XY] != 0 
     ORDER BY [row_num] DESC) Q
OUTER APPLY
    (SELECT TOP 1 [CT_CR1]  
     FROM [Obe].[dbo].[vFan] 
     WHERE [row_num] <= P.[row_num] 
       AND [CT_CR1] IS NOT NULL 
       AND [CT_CR1] != 0 
     ORDER BY [row_num] DESC) R
OUTER APPLY
    (SELECT TOP 1 [CT_CR2] 
     FROM [Obe].[dbo].[vFan] 
     WHERE [row_num] <= P.[row_num] 
       AND [CT_CR2] IS NOT NULL 
       AND [CT_CR2] != 0 
     ORDER BY [row_num] DESC) S
ORDER BY 
    P.[row_num] ASC

Order By inside the Outer Apply is very costly, is there any better way to write this query ?

Answer

From what I can tell in the code, you seem to be implementing a variation of lag(ignore nulls).

Here is a better way:

select f.*,
       max(ct_xy) over (partition by rn_xy) as new_ct_xy,
       max(rn_cr1) over (partition by rn_cr1) as new_rn_cr1,
       max(rn_cr2) over (partition by rn_cr2) as new_rn_cr2
from (select f.*,
             sum(case when CT_XY is not null and CT_XY <> 0 
                      then row_num
                 end) over (order by row_num) as rn_xy,
             sum(case when CT_CR1 is not null and CT_CR1 <> 0 
                      then row_num
                 end) over (order by row_num) as rn_cr1,
             sum(case when CT_CR2 is not null and CT_CR2 <> 0 
                      then row_num
                 end) over (order by row_num) as rn_cr2
      from vfan f 
     ) f;

Also note that in many databases the “v” in vfan would suggest a view. That might be the cause of the performance issues.