title: Using DISTINCT with UNION/UNION ALL
body: <p>I've stumbled across a slow-performing section of code that looks alot like this.</p>
  
  <pre><code>SELECT
    res.[X],
    res.[Y],
    SUM(res.[Z]) -- This is SUM so I have to remove duplicates
  FROM (
    SELECT DISTINCT a.[X], a.[Y], b.[Z] FROM [A] a JOIN [B] b ON a.[ID] = b.[ID]
    UNION
    SELECT DISTINCT a.[X], a.[Y], c.[Z] FROM [A] a JOIN [C] c ON a.[ID] = c.[ID]
    UNION
    SELECT DISTINCT a.[X], a.[Y], d.[Z] FROM [A] a JOIN [D] d ON a.[ID] = d.[ID]
    UNION ALL -- This set won't have duplicates, hence the UNION ALL in this case
    SELECT a.[X], a.[Y], n.[Z] FROM [A] a JOIN [N] n ON a.[ID] = n.[ID]
  ) res
  GROUP BY res.[X], res.[Y]
  </code></pre>
  
  <p>The joins are alot more complex and there are 12 of these UNION/UNION ALLs, but you get the picture. Each result set usually contains between 1 and 15 million rows.</p>
  
  <p>I'm wondering how anyone else would write this query. I read a couple other threads that warn against:</p>
  
  <pre><code>SELECT DISTINCT * FROM [A]
  UNION
  SELECT DISTINCT * FROM [B]
  </code></pre>
  
  <p>because the DISTINCT is called three times (in this small example). So I gave that a shot and removed the DISTINCT. The result was actually alot slower. I don't understand how removing extra filtering would cause the query to run slower.</p>
  
  <p>Does anyone have any ideas? I'm digging into the query plan, but it's much too large to post so I'm just looking for suggestions. Thanks!</p>