database - SQL Server query involving subqueries - performance issues -

i have 3 tables:

table 1: | dbo.pc_a21a22 |

    batchnbr                   other columns...     --------                   ----------------       12345         12346       12347 

table 2: | dbo.outcome |

   passageid                 record    ----------               ---------       00003                    200       00003                     9       00004                     7 

table 3: | dbo.passage |

   passageid                 passagetime        batchnbr    ----------               -------------       ---------       00001                   2015.01.01         12345       00002                   2016.01.01         12345       00003                   2017.01.01         12345       00004                   2018.01.01         12346 

what want do: each batchnbr in table 1 first latest passagetime , corresponding passageid table 3. passageid, relevant rows in table 2 , establish whether of these rows contains record 200. per passageid there @ 2 records in table 2

what efficient way this?

i have created query works, it's awfully slow , unfit tables millions of rows. suggestion on how either change query or way? altering table structure not option, have read rights database.

my current solution (slow):

select top 50000     a.batchnbr,     cast ( case when 200 in (select top 2 record dbo.outcome passageid in (     select subqueryresults.passageid (select top 1 passageid dbo.passage pass pass.batchnbr = a.batchnbr order passagetime desc) subqueryresults      )      ) 1 else 0 end bit) kgt_io_end      dbo.pc_a21a22 

the desired output is:

batchnbr     200present ---------    ---------- 12345            1 12346            0 

i suggest use table joining rather subqueries.

select      a.*, b.*      dbo.table1  join      dbo.table2 b on a.id = b.id       /*your clause filtering*/ 

edit:

you use reference join vs. sub-query