tsql - Is CROSS APPLY the best way or possibly WHEN EXISTS or a subquery? -

i relatively inexperienced when comes sql. wondering if using cross apply best option in sql below?

i think duplicating work. works , takes few minutes feeling can done better.

the 3 main tables i'm looking @ quite large , on own looking @ couple of million rows each. individual insert statements pulling around 7-15k rows each.

---

declare @master table   (      heyno              nvarchar(12),      postcodestartdttm  date,      postcodeenddttm    date,      lzohistorypostcode nvarchar(25),      biactivitypostcode nvarchar(25),      activityenddttm    date   );  insert @master select p.pasid,        par.startdttm,        par.enddttm,        pa.postcode lzohistorypostcode,        t.postcode  biactivitypostcode,        t.admitdate   healthbi.dbo.[lzo_patientaddressrole] par        inner join healthbi.dbo.[lzo_patientaddress] pa                on par.[addressoid] = pa.[oid]                   , pa.[status] = 'a'        inner join healthbi.dbo.lzo_patient p                on par.identifyingoid = p.oid        ---         cross apply (select i.admitdate,                            i.postcode                       healthbi_views.dbo.ip_admission                      i.patientoid = p.oid                            , i.admitdate > par.startdttm                            , i.admitdate >= '01 april 2010'                            , i.admitdate < isnull(par.enddttm, '31-dec-4712')                            , par.[status] = 'a'                            , par.[identifyingtype] = 'patient'                            , par.[rotypcode] = 'cc_usualadd'                            , pa.[adtypcode] = 'address'                            , pa.postcode <> i.postcode) t  insert @master select p.pasid,        par.startdttm,        par.enddttm,        pa.postcode lzohistorypostcode,        t.postcode  biactivitypostcode,        t.apptstartdate   healthbi.dbo.[lzo_patientaddressrole] par        inner join healthbi.dbo.[lzo_patientaddress] pa                on par.[addressoid] = pa.[oid]                   , pa.[status] = 'a'        inner join healthbi.dbo.lzo_patient p                on par.identifyingoid = p.oid        cross apply (select i.apptstartdate,                            i.postcode                       healthbi_views.dbo.op_appointment                      i.patientoid = p.oid                            , i.apptstartdate > par.startdttm                            , i.apptstartdate >= '01 april 2010'                            , i.apptstartdate < isnull(par.enddttm, '31-dec-4712')                            , par.[status] = 'a'                            , par.[identifyingtype] = 'patient'                            , par.[rotypcode] = 'cc_usualadd'                            , pa.[adtypcode] = 'address'                            , pa.postcode <> i.postcode) t  insert @master select p.pasid,        par.startdttm,        par.enddttm,        pa.postcode lzohistorypostcode,        t.postcode  biactivitypostcode,        t.attenddate   healthbi.dbo.[lzo_patientaddressrole] par        inner join healthbi.dbo.[lzo_patientaddress] pa                on par.[addressoid] = pa.[oid]                   , pa.[status] = 'a'        inner join healthbi.dbo.lzo_patient p                on par.identifyingoid = p.oid        cross apply (select i.attenddate,                            i.postcode                       healthbi_views.dbo.ed_attendance                      i.patientoid = p.oid                            , i.attenddate > par.startdttm                            , i.attenddate >= '01 april 2010'                            , i.attenddate < isnull(par.enddttm, '31-dec-4712')                            , par.[status] = 'a'                            , par.[identifyingtype] = 'patient'                            , par.[rotypcode] = 'cc_usualadd'                            , pa.[adtypcode] = 'address'                            , pa.postcode <> i.postcode) t  select m.heyno,        m.lzohistorypostcode,        m.biactivitypostcode,        d.startofweek   @master m        inner join healthbi_views.dbo.date_reference d                on m.activityenddttm = d.datevalue group  m.heyno,           m.lzohistorypostcode,           m.biactivitypostcode,           d.startofweek order  m.heyno  

you not want use cross apply. meant used table value functions , works little bit cursor, 1 record @ time, slower set operations.

i try use outer join instead below. it's hard right without understanding data better, seems each patient multiple admission dates , post codes, may have duplication depending on ip_addmission view returns. if not desired, can using group return latest admission date example.

also, avoid using functions in clause can cause table scan. replaced isnull or expression below.

select p.pasid,        par.startdttm,        par.enddttm,        pa.postcode lzohistorypostcode,        i.postcode  biactivitypostcode,        i.admitdate   healthbi.dbo.[lzo_patientaddressrole] par        inner join healthbi.dbo.[lzo_patientaddress] pa                on par.[addressoid] = pa.[oid]                   , pa.[status] = 'a'        inner join healthbi.dbo.lzo_patient p                on par.identifyingoid = p.oid        ---         left outer join healthbi_views.dbo.ip_admission         on i.patientoid = p.oid   i.admitdate > par.startdttm                            , i.admitdate >= '01 april 2010'                            , (i.admitdate < par.enddttm or par.enddttm null)                            , par.[status] = 'a'                            , par.[identifyingtype] = 'patient'                            , par.[rotypcode] = 'cc_usualadd'                            , pa.[adtypcode] = 'address'                            , pa.postcode <> i.postcode;