CODEWITHSUNDEEP
sqlsql-server
Jayasree
Jayasree

Reputation: 9

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 ?

Upvotes: 0

Views: 111

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

StepUp
StepUp

Reputation: 38094

When you use ORDER BY, SQL Server must compute all rows, sort then and only then can it give you the rows that you asked for.

So it would be great to add indexes for fields used in WHERE statement such as CT_XY, CT_CR1, CT_CR2.

UPDATE:

You should not add indexes to view, it is necessary to add indexes to the underlying table. Your query will use the indexes, where appropriate, on the underlying tables the view itself refers to.

Upvotes: 0

Related Questions