title: Slow running query with full outer join
body: <p>Good morning stackoverflow.</p>
  
  <p>I have a query that is running very slow (takes about 25 seconds to return 50 rows).
  Basically I have 2 inner queries inside a larger query, the 2 inner queries run quickly separately but when I join them with a full outer join it gets very slow.</p>
  
  <p>Here is my code:</p>
  
  <pre><code>DECLARE @StartDate DATETIME, @EndDate DATETIME
  
  SET @StartDate = '20131106'
  SET @EndDate = '20131107';
  
  WITH al
       AS (SELECT at.Date AS 'Note Date',
                  at.ApplicationID,
                  at.Comment,
                  at.UserID
           FROM   ApplicationTimeline AS at
           WHERE  ( at.[Date] BETWEEN @StartDate AND @EndDate + '23:59:59' )
                  AND ( at.EventID = 1 ) -- UW notes
                  AND ( at.Title = 'Allocation Note' )),
       cm
       AS (SELECT cm.IdentityID,
                  cm.ApplicationID,
                  cm.ReminderFeedback,
                  cm.timestamp,
                  (SELECT dbo.udf_StripHTML(cm.Comments)) AS 'Comments'
           FROM   tblCaseManagement AS cm
           WHERE  cm.timestamp BETWEEN @StartDate AND @EndDate)
  SELECT a.ApplicationID,
         a.LogDate,
         cmu.ShortName,
         cm.ReminderFeedback,
         cm.timestamp
  FROM   al
         FULL OUTER JOIN cm
           ON cm.ApplicationID = al.ApplicationID
         INNER JOIN tblApplication AS a
           ON a.ApplicationID = ISNULL(al.ApplicationID, cm.ApplicationID)
         INNER JOIN tblUser AS cmu
           ON cmu.IdentityID = cm.IdentityID 
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/eh7jD.png" alt="Execution plan">
  <br/>
  I might end up writing this as a union but I really want to know why this is so slow.
  Any help will be greatly appreshiated, thank you for your time.
  Bobby</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I have found that saving the inner queries into two temp tables then applying the full outer join is super fast.</p>
  
  <p>I don't know why this is improving the query speed so much.</p>