Simple SQL UNION ALL very slow

Question

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.

SELECT 
     1
FROM 
     [fnGetEmployeeProfileDoctorsMultiplePeriods](
          @FromPeriodId, 
          @ToPeriodId, 
          @ProfileId, 
          @EmployeeId, 
          @GeographicalAreaId, 
          @DoctorId, 
          @TeamId
     )
UNION ALL   
SELECT 
     1
FROM 
     [fnGetEmployeeProfileOrganizationsMultiplePeriods](
          @FromPeriodId, 
          @ToPeriodId, 
          @ProfileId, 
          @EmployeeId, 
          @GeographicalAreaId, 
          @DoctorId, 
          @TeamId
      )

Update:

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?

Plan links:

Slow version

https://www.brentozar.com/pastetheplan/?id=SyZq2Ybbb

Quick version with option(recompile) set

https://www.brentozar.com/pastetheplan/?id=HJ5bpYbZZ

Update 2:

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.

user_3046585 · Answer

I was having the same issue.

--Requete 67. Performance cost 6%
SELECT * 
INTO #temp_arbo_of_6 
FROM (
SELECT * FROM #temp_arbo_of_4
UNION ALL
SELECT * FROM #temp_arbo_of_5
) AS tmp;

each table has 60 columns. Takes up 6% of the global timing of an analytic stored procedure. It shouldn't.

Solved it this way:

--Requete 67. Performance cost 0%
INSERT INTO #temp_arbo_of_4
SELECT * FROM #temp_arbo_of_5;

and then using #temp_arbo_of_4 instead of #temp_arbo_of_6. I didn't need both.

Simple SQL UNION ALL very slow

3 Answers