title: COUNT(1) + COUNT(DISTINCT()) much slower then doing 2 queries separately
body: <p>Query explanation:</p>
  
  <ul>
  <li><code>Person</code> (identified by <code>PersonID</code>) may have or not have a corresponding <code>Job</code> (identified by <code>JobID</code>).</li>
  <li>If there's a corresponding <code>Job</code>, binding is stored in table <code>PersonJob</code> (<code>PersonID</code> &lt;=> <code>JobID</code>).</li>
  <li><code>Person</code> without <code>Job</code> are ignored.</li>
  <li><code>Job</code> also has <code>CityID</code>.</li>
  <li>Per every <code>Job.CityID</code>, query wants to know total count of <code>Person</code> and also count of unique <code>Person.HouseID</code></li>
  </ul>
  
  <p>Query:</p>
  
  <pre><code>SELECT
    Job.CityID, COUNT(1) NumTotal, COUNT(DISTINCT(Person.HouseID)) NumDistinct
  FROM
    Job
    INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
    INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
  GROUP BY
    Job.CityID
  </code></pre>
  
  <p>Stats:</p>
  
  <ul>
  <li><code>SELECT COUNT(1) FROM PersonJob</code> ~ 600.000</li>
  <li><code>SELECT COUNT(1) FROM Person</code> ~ 800.000</li>
  <li><code>SELECT COUNT(DISTINCT(Person.HouseID)) FROM Person</code> ~ 10.000</li>
  <li><code>SELECT COUNT(1) FROM Job</code> ~ 500</li>
  <li>MS SQL Server 10.50</li>
  </ul>
  
  <p>Problem:</p>
  
  <ul>
  <li><p><code>COUNT(1)</code> part of query, when run separately, runs in 0.25sec.</p>
  
  <pre><code>SELECT
    Job.CityID, COUNT(1) NumTotal
  FROM
    Job
    INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
    INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
  GROUP BY
    Job.CityID
  </code></pre></li>
  <li><p><code>COUNT(DISTINCT(Person.HouseID))</code> part of query, when run separately, runs in 0.80sec.</p>
  
  <pre><code>SELECT
    Job.CityID, COUNT(DISTINCT(Person.HouseID)) NumDistinct
  FROM
    Job
    INNER JOIN PersonJob ON (PersonJob.JobID = Job.JobID)
    INNER JOIN Person ON (Person.PersonID = PersonJob.PersonID)
  GROUP BY
    Job.CityID
  </code></pre></li>
  <li><p>Entire query runs in 3.10sec  - <strong>3 times slower, why?</strong></p></li>
  </ul>
  
  <p>Execution plans:</p>
  
  <ul>
  <li>I'm no expert in reading those, sorry.</li>
  <li>As far as I can tell, the problem lies within COUNT(DISTINCT)</li>
  <li>In partial query:
  
  <ul>
  <li>25% Hash match (Aggregate) (output <code>Job.CityID</code>)</li>
  <li>15% Hash match (Inner Join) (output <code>Job.CityID</code>, <code>Person.HouseID</code>)
  
  <ul>
  <li>30% Index scan (output <code>Person.PersonID</code>, <code>Person.HouseID</code>)</li>
  <li>14% Index seek (output <code>PersonJob.PersonID</code>)</li>
  </ul></li>
  </ul></li>
  <li>In full query:
  
  <ul>
  <li>03% Hash match (Partial aggregate) (output <code>Job.CityID</code>, <code>COUNT(*)</code>)</li>
  <li>31% Hash match (Aggregate) (output <code>Job.CityID</code>)</li>
  <li>29% Table Spool (output <code>Job.CityID</code>, <code>Person.HouseID</code>)</li>
  </ul></li>
  </ul>