Forum Discussion
Direct query using count distinct hitting database 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.