title: Left Join with OR taking forever to run
body: <p>I've spent a long writing a join that finally gets all the criteria I need, but it's taking forever to run. Any ideas on how to speed this up? I've attached a link to the query plan below, however I'm not 100% sure how to read it and find the choke point of the query. </p>
  
  <pre><code>SELECT
    A.procedure_code
    ,a.modifier_one
   ,A.invoice_id
   ,A.invoice_service_date
   ,A.procedure_total_billed_fee
   ,DATEADD(month, DATEDIFF(month, 0, A.invoice_service_date), 0) AS VisitMonth
   ,A.facility_name
   ,A.invoice_insert_date
   ,DATEADD(month, DATEDIFF(month, 0, A.invoice_insert_date), 0) AS InvoiceInsertMonth
   ,A.claim_entry_date
   ,A.claim_last_modify_date
   ,A.primary_insurance_name
   ,A.primary_payer_level_three
   ,A.patient_id
   ,A.procedure_id
   ,D.[MedicareRate]
   ,a.cms_locality_name
  FROM
   CIPBackUps.cip.[2020_02_05_month_end_services] A
   LEFT JOIN (SELECT [MedicareRate],[Locality Name],[Locality Number],HCPCS, [mod] FROM TESTDB.dbo.[medicare2018] WHERE [Medicare Administrative Contractor (MAC)] = '4412')  D 
    ON a.cms_locality_number = D.[Locality number] AND 
     (
      (concat(A.procedure_code,a.modifier_one) = concat(D.HCPCS,d.mod) AND a.modifier_one in ('','TC','26','53') AND procedure_code NOT IN ('99281','99282','99283','99284','99285','99291','99292') ) 
      OR (A.procedure_code = D.HCPCS AND a.modifier_one NOT in ('','TC','26','53')
      OR (A.procedure_code = D.HCPCS AND (a.modifier_one IS NULL or procedure_code IN ('99281','99282','99283','99284','99285','99291','99292')) )) AND A.procedure_code NOT LIKE 'G%'
     )
  WHERE 
   procedure_total_billed_fee &lt;&gt; 0
   AND a.invoice_service_date between '10/01/2018' AND '12/31/2018 23:59:59'
  </code></pre>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=r1sVLaHV8" rel="nofollow noreferrer">Paste the Plan Link</a></p>