title: Performance using DISTINCT COUNT
body: <p>I am running SQL Server 2012.</p>
  <p>I have a query that when striped to its most basic form looks like this:</p>
  <pre><code>SELECT COUNT(DISTINCT fullAddress) as quickCount 
  FROM leads
  WHERE yearID &gt;=12 AND yearID &lt;=21
  </code></pre>
  <p>The leads table has about 149 Million records in it. There is a clustered index on the leadID and a non-clustered index that is indexes on YearID and has an include for fullAddress.</p>
  <p>This query as it is takes about 40 secs to run. I realize that is not bad but in this situation that is just not fast enough.</p>
  <p>I looked at the execution plan and from what I can tell about 60% of the cost is the DISTINCT COUNT.</p>
  <p>When I run the same query without the DISTINCT COUNT like this:</p>
  <pre><code>SELECT COUNT(*) as quickCount 
  FROM leads
  WHERE yearID &gt;=12 AND yearID &lt;=21
  </code></pre>
  <p>It takes only 1 sec to run.</p>
  <p>Unfortunately, I need to get a count of distinct full addresses. So I am trying to figure out if there is anything I can do to make the first query run faster.</p>
  <p>Here is a screenshot of the execution plan for both queries:</p>
  <p><img src="https://i.stack.imgur.com/yghii.jpg" alt="enter image description here" /></p>
  <p>Here is a link to that to see it bigger - <a href="http://www.sequenzia.com/execPlan.jpg" rel="nofollow noreferrer">http://www.sequenzia.com/execPlan.jpg</a></p>
  <p>From what I can tell my main problem is the Distinct Sort (52%).</p>
  <p>Any help or feedback on this would be great.</p>
  <p>Thanks!</p>
  <p><strong>UPDATE</strong></p>
  <p>I took Thilo's advice and applied this index:</p>
  <pre><code> CREATE INDEX IDX_X ON LEADS(FULLADDRESS, YEARID);
  </code></pre>
  <p>I actually created 2 new test tables with the exact same 1 Million records in each of them. I applied my same original index to both and then the above index to just one. Now when I compare the the 2 tables on the same execution plan the one with the above index is a little better 48% to 52%. Here is the new execution plan - <a href="http://www.sequenzia.com/execPlan2.jpg" rel="nofollow noreferrer">http://www.sequenzia.com/execPlan2.jpg</a></p>
  <p>That helps some but I really need more performance. Any other ideas out there?</p>