Forum Discussion
Direct query using count distinct hitting database for every row
Unfortunately it isn't, it's the unique_id of an event that is duplicated in some cases in this particular table, which is why we need the distinct. To me - it seems that the underlying query should just be able to switch from doing the SUM(IS_PAGE_VIEW) to COUNT(DISTINCT UNIQUE_ID) with it querying once for all the values (like in the first query). It's not clear to me why it's switching from one type of query to another. The table it's querying is a large one with around 300 million rows, and it's just not going to handle running this query against it for every row.
Super UserOne thing you can consider is indexing the living daylights out of your source table, using the Direct Query queries as guidance. Of course this comes with a large cost in terms of storage and maintenance, but it may buy you the required performance.
We use Snowflake where you don't do your own indexing, so this isn't gong to help.
What I really want is the query that hits the database to look like the below, what I don't understand is why PowerBI doesn't do this - and if there's anything I can do to suggest that it constructs the query like this instead. Each query is against the 300 million row table, which is fine when it runs once - it takes about a second, but clearly when it does this once for each of 100+ users this is very inefficient and slow.
select "USER_AND_SHORT_CONTRACT_KEY",
count(distinct unique_id) as "C1"
from
(
select "UNIQUE_ID",
"DATE_TIME",
... lots of columns
from "PROD_MACI"."MACI_SEMANTIC"."FACT_REGISTERED_USER_PAGE_VIEWS"
where ("USER_AND_SHORT_CONTRACT_KEY" in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?))
) as "ITBL"
group by "USER_AND_SHORT_CONTRACT_KEY"
LIMIT 1000001 OFFSET 0- lbendlin
Raise it as an idea and/or issue against the Power Query team to ask for options to include query hints during folding. Not sure if the Snowflake connector supports custom queries but if it does then handcrafting your queries might be another option. Of couse this will likely break query folding.