on 01 Feb 2019 23:43:45
I create a query in Power BI Desktop against a data source. Power BI Desktop insists on executing it multiple (usually three) times. This makes Desktop slow and for back-end data sources exerts additional load that can also incur additional costs.
Note: I am not talking about the scenario where a query is referred to multiple times in M, rather just a simple query loaded into one table in the data model. I assume the engine is checking data types, etc - but the current approach is very slow/painful. Optimisation is needed!
- Comments (15)
RE: Stop Executing Queries Multiple Times
This is unacceptable, running queries multiple times is degradding our server performance.
MS should fix this MAJOR ISSUE.
RE: Stop Executing Queries Multiple Times
How is this not addressed yet. We are facing lot of performance issues querying Hive with billions of rows. The power bi file has typically multiple tables used and each of the table executes 3 queries internally.
RE: Stop Executing Queries Multiple Times
How is this still not resolved?
RE: Stop Executing Queries Multiple Times
REST API calls are sending almost 10 queries for datasets larger than 10k records before loading. (PBI Server desktop Sept. 2019 version)
RE: Stop Executing Queries Multiple Times
A decent article/vid on this behavior can be found here:
https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/
I think the extra querying of metadata is the most difficult to resolve. The workaround in the vid of manually specifying data structure in M code isn't realistic for most people.
RE: Stop Executing Queries Multiple Times
This is becoming a significant concern for us as well. Power BI seems to be putting an unnecessary load on our source systems.
RE: Stop Executing Queries Multiple Times
What type of source are you having issues with? I was seeing the behaviour you describe with Excel files on SharePoint and found that defining them as "From Web" rather than "From Excel" means that they are cached and not reread
RE: Stop Executing Queries Multiple Times
No information from Power BI team?
At least please let us know why it happens
RE: Stop Executing Queries Multiple Times
Not sure if I have the exact same problem as the OP but working on the data model in complex cases is very painful and it needs to be easier to jump between transformations to debug issues without issuing a fresh recall of data
RE: Stop Executing Queries Multiple Times
Absolutely ridiculous that this happens. I try to refresh a dashboard, the query for 2 years takes 30 minutes. Three executions of the query (2 of which are unnecessary) take 1 hour 30. Not sure why this is.