title: Simple SQL UNION ALL very slow
body: <p>The sql statement is a simple union all between two queries. Each one on its own is instantaneous. Union all them however and it becomes 20x slower. Any help is appreciated.</p>
  
  <pre><code>SELECT 
       1
  FROM 
       [fnGetEmployeeProfileDoctorsMultiplePeriods](
            @FromPeriodId, 
            @ToPeriodId, 
            @ProfileId, 
            @EmployeeId, 
            @GeographicalAreaId, 
            @DoctorId, 
            @TeamId
       )
  UNION ALL   
  SELECT 
       1
  FROM 
       [fnGetEmployeeProfileOrganizationsMultiplePeriods](
            @FromPeriodId, 
            @ToPeriodId, 
            @ProfileId, 
            @EmployeeId, 
            @GeographicalAreaId, 
            @DoctorId, 
            @TeamId
        )
  </code></pre>
  
  <p><strong>Update</strong>:</p>
  
  <p>It seems that adding the union operator causes the execution plan for the two sub-queries to change; the query engine does not simply concatenate the two original plans. Using the option recompile flag fixes this (or rather, creates a much faster plan), so how can one force it to use the recompiled plan instead?</p>
  
  <p>Plan links:</p>
  
  <p>Slow version</p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=SyZq2Ybbb" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=SyZq2Ybbb</a></p>
  
  <p>Quick version with option(recompile) set</p>
  
  <p><a href="https://www.brentozar.com/pastetheplan/?id=HJ5bpYbZZ" rel="nofollow noreferrer">https://www.brentozar.com/pastetheplan/?id=HJ5bpYbZZ</a></p>
  
  <p><strong>Update 2:</strong></p>
  
  <p>After much optimization in the two subqueries, the symptoms remain, but the execution plans now don't change on adding the union operator. Also, if you use a top operator in a way that obtains the results from only the first subquery (or, surprise, only the second one), the results come back immediately. 'Cross the border' and request results from both however, and a 5 second penalty is added. </p>