What is the alternative for outer apply?

Question

Recently I have added outer apply in my a query. Since then this query takes forever. One reason i know that the table it is associated with is the biggest table in the database now.

select   
    a.*,
    b.*,
    BTab.*,
    BTTab.*
from 
    tableA a 
    join tableB b ON a.ID = b.UID
    join *****
    left join *******
    ....
    ....
    ....
    outer apply 
        (SELECT TOP 1 * 
        FROM 
            biggestTable bt 
        WHERE 
            bt.id = a.id 
            and a.id <> 100 
        ORDER BY a.datetime desc) BTab
    Outer apply
        (SELECT TOP 1 * 
        FROM 
            biggestTable btt
        WHERE 
            btt.id = a.id 
            AND btt.DateTime <> '1948-01-01 00:00:00.000'
            and btt.value = 0 
        order by btt.datetime desc) BTTab
where 
    ..................
    .................
    ....................
    .................

Is there any better solution than using outer apply?

Answer 1

Here's an alternative, can't say whether its better or not. You may simply need better indexes on your big table

WITH BTAB as 
(       SELECT TOP 1 
            * ,
            row_nubmer() over (partition by b.id) rn 

        FROM 
            biggestTable bt 
) ,
BTTab as (SELECT TOP 1 
              * ,
              row_nubmer() over (partition by btt.id order by btt.datetime desc) rn 
        FROM 
            biggestTable btt
        WHERE 
            AND btt.DateTime <> '1948-01-01 00:00:00.000'
            and btt.value = 0 
)

select   
    a.*,
    b.*,
    BTab.*,
    BTTab.*
from 
    tableA a 
    join tableB b ON a.ID = b.UID
    join *****
    left join BTab on ON a.ID = BTab.ID 
           and BTAB.rn = 1
    left join BTTabon ON a.ID = BTTab.ID 
           and BTTab.rn = 1