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

+1 for Conrad as his answer might be all you need and I reused some of his syntax.

Problem with Apply and CTE is they are evaluated for each row in the a, b join.

I would create two temporary tables. To represent the max rows and put a PK on them. The benefit is these two expensive quires are done once and the join is to a PK. Big benefit joining to a PK. I eat the overhead of #temp to get a single evaluation and PK a lot.

   Create table #Btab (int ID PK, ...)
   insert into #Btab
   WITH BTAB as 
   (       SELECT * ,
               row_nubmer() over (partition by b.id) rn 
           FROM 
               biggestTable
           where ID <> 100 
   ) 
   Select * from BTAB
   Where RN = 1 
   order by ID

   Create table #Bttab (int ID PK, ...)
   insert into #Bttab
   WITH BTTAB as 
   (       SELECT * ,
               row_nubmer() over (partition by id order by datetime desc) rn 
           FROM 
               biggestTable
           where DateTime <> '1948-01-01 00:00:00.000' and value = 0
   ) 
   Select * from BTAB
   Where RN = 1 
   order by ID

   select   
       a.*,
       b.*,
       #Btab.*,
       #Bttab.*
   from 
       tableA a 
       join tableB b ON a.ID = b.UID
       join *****
       left join *******
       ....
       ....
       ....
       left outer outer join #Btab 
           on #Btab.ID = a.ID
       left outer outer join #Bttab 
           on #Bttab.ID = a.ID
   where 
       ..................
       .................

P.S. I am exploring TVP over #TEMP for this. A TVP supports a PK and has less overhead than #tmp. But I have not compared them head to head in this type of application.

Tested TVP over #TEMP and got a 1/2 second improvement (about the time it take to create and delete a temporary table).

Answer 2

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