Performance using DISTINCT COUNT

Question

I am running SQL Server 2012. I have a query that when striped to its most basic form looks like this: ``` SELECT COUNT(DISTINCT fullAddress) as quickCount FROM leads WHERE yearID >=12 AND yearID <=21 ``` 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. 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. I looked at the execution plan and from what I can tell about 60% of the cost is the DISTINCT COUNT. When I run the same query without the DISTINCT COUNT like this: ``` SELECT COUNT(*) as quickCount FROM leads WHERE yearID >=12 AND yearID <=21 ``` It takes only 1 sec to run. 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. Here is a screenshot of the execution plan for bot…

Accepted Answer

One thing to try is to get rid of the sorting, by having an index ordered on `fullAddress` (that also includes the `yearID` column so that you can satisfy the where clause, too).

```
CREATE INDEX IDX_X ON LEADS(FULLADDRESS, YEARID);
```

This way, you should get a Fast Full Index Scan (probably still slower than the Index Range Scan you have for non-distinct count, but hopefully faster than your 40s sorting).

But why does it need to be so fast? This is not something you need to do all the time, right? If this is for a public web site, you can get away with a slightly outdated cached result, I would think.

Performance using DISTINCT COUNT

Solution

Problem