Forum Discussion
Power BI runs query multiple times on backend?
I'm developing a dashboard using Apache Hive as the backend storage (using an ODBC driver) and I've noticed something strange.
When I add a datasource, Power BI runs the query 3 times for each refresh. Some of these queries are quite complex on huge datasets, with many joins that can take > 15 minutes to complete. Having Power BI run the exact same query 3 times is causing issues.
What is Power BI doing, and is there any way to get it to not run the same query multiple times on each refresh?
I'm on Power BI version 2.47.4766.542 64-bit (June 2017)
34 Replies
cbailissAdvocate II
The repeated running of the same query is a pain that affects multiple sources.
I raised an idea:
If you feel strongly, vote for it.
Has this been solved yet? It's June 2018 and it's still happening for me using Hive DB.
farendtWe are also experiencing the issue and with increased usage of Power BI, it is starting to negatively affect performance of Hive for all users.
Before import I was using data query connection but it was worst, Power BI executed 10 queries at the same time, the DBA came to look for me and almost cancelled my DB user :smileylol: because the DB server was working at 100%.
After that I tried with import query, optimized the execution plan of my query and it's working better but I realized PBI executes it 3 times, at least not at the same time.
If someone finds the solutions or has some answer of microsoft please let us know.
hello,
I'm running exactly into the same issue using Apache Hive as back end data storage. any query coming from Power BI is run 3 times and this lead to a high latency while refreshing our report.
did you solve it on your side? if yes, how please?
thanks for repy.
sidoine.
This is simply unacceptable. This is the definition of death by a thousand cuts. We have over 60 dashboards spread across 3 servers and that's still not enough to off load the issue this is creating. If a dashboard is pulling in 5 tables thats 10 connections and executions to pull one dashboard.
In the examply below I ran a trace on the dashboard refresh and you can clearly see the query running twice with a connection reset inbetween. It's impossible to negate the negative effect of this many connections even to multiple servers.
The Problem occurs when Power BI issues a Hive Query something like 'SELECT * FROM DATE' but if imported as the table it uses only one connection. Hope this helps
Thanks
I have the same issue. Used Profiler to trace and each refresh of parameters from the Visuals view of Power BI Desktop causes the stored procedure to run twice. The first execution takes 2.6 seconds, the 2nd execution takes 35 seconds. Is the first execution just to return metadata? The parameters appeared to be the same, but different SPID.
Testing from the 'Edit queries' view, the execution only occurs once.
hello,
I'm running exactly into the same issue using Apache Hive as back end data storage. any query coming from Power BI is run 3 times and this lead to a high latency while refreshing our report.
did you solve it on your side? if yes, how please?
thanks for repy.
sidoine.
ugurdemirHelper II
It's 2024 and problem still exists in SQL Server connection.
I think Microsoft team has lost on the way of solving this problem. Otherwise they would give a written answerhere is my test script,
let Source = Sql.Database(".", "TEST", [Query="select * from [dbo].[ProductSold]"]), selectARow = Table.SelectRows(Source, each [ProductID] = 3 ), selectAColumn = Table.SelectColumns(selectARow,{"ProductID"})[ProductID]{0}, testProc = Sql.Database(".", "TEST", [Query="exec test2 " & Text.From(selectAColumn)]) in testProcIn this situation "select * from [dbo].[ProductSold]" query runs only once but the "exec test2 " query runs twice.
