As a follow-on from last week’s post about the new ApproximateDistinctCount function in DAX, I want to share something I found out from my recent testing for this function: ApproximateDistinctCount is sometimes slower than DistinctCount. This sounds counter-intuitive, right? After all ApproximateDistinctCount is a performance optimisation for the sometimes slow, expensive DistinctCount function. But it turns out that there are certain engine optimisations for DistinctCount that can’t be replicated for ApproximateDistinctCount which mean that for low-cardinality columns using DistinctCount can be faster.
Last week’s post showed an example of where ApproximateDistinctCount wins so I won’t bother showing another one. Consider, though, the following, very basic Direct Lake semantic model consisting of just the 2017 data from the New York Taxi dataset:
The table itself has only got 11.7 million rows and the VendorId column on it only contains two distinct values. I built two measures in this model:
Distinct Vendor ID = DISTINCTCOUNT('green_tripdata_2017'[VendorID])Approx Distinct Vendor ID = APPROXIMATEDISTINCTCOUNT('green_tripdata_2017'[VendorID])
Running a DAX query that returns the Distinct Count version of the measure for a lot (85562) of rows on a cold cache gives the following performance:
As you can see, the query itself takes an impressive 188ms and the single scan uses just 110ms of CPU time. The memory usage for this query was only 8028KB.
Now look at the same query but for the Approximate Distinct Count measure:
It’s still fast but it now takes 688ms and the scan uses 547ms of CPU time. Memory usage was also higher 410441KB – the current implementation prioritises performance over memory overhead. These numbers are small and the lesson is an old one: test first before you make any changes in production and only think about making changes if you’re sure DistinctCount is actually causing problems. Performance may change in the future too: one optimisation rolled out this week already that drastically improved the performance of ApproximateDistinctCount in my tests.
The next question you have will probably be this: if ApproximateDistinctCount is slower on low-cardinality columns, how low is “low”? After all you probably aren’t going to be doing a distinct count on a column with only two distinct values in it or running many DAX queries that return 86000 rows. Guess what: the answer is that it depends and again you’ll have to test. I saw worse performance for ApproximateDistinctCount on a column with around 11000 distinct values in it but your mileage may vary.
Does this mean ApproximateDistinctCount is useless? Not at all! If you have a very large semantic model and you’re trying to do a distinct counts on a column with millions of distinct values then it can be a lifesaver. Just don’t use it indiscriminately.
[Thanks to Bismarck Hsu for the information in this post]