Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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)

33 REPLIES 33
bullyandrufus
Regular Visitor

Has this been solved yet? It's June 2018 and it's still happening for me using Hive DB. 

Anonymous
Not applicable

I'm still experiencing the same issue with Hive queries using ODBC drivers, both in the online version and on the desktop version.

We are also experiencing the issue and with increased usage of Power BI, it is starting to negatively affect performance of Hive for all users.

sidoine
New Member

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.

Anonymous
Not applicable

I could not solve it, I only optimized my queries and brought only data really needed in that dashboard

sidoine
New Member

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.

Anonymous
Not applicable

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.

wlagraba
Regular Visitor

I see the same behavior when the query uses the Web.Contents ("Web" datasource) function. I have a REST web service that returns a CSV formatted payload and my logs show 3 executions of the same data query without regard of the payload size.

 

When the payload size is really small (3 rows in the CSV content) the 3 query runs happen on every "Refresh". When the payload is larger (10,000 rows) I'm seeing 6 query runs where the first 5 seem to be cut/stopped by Power BI around row 5,000 on the first 5 runs (getting a "Connection Reset by Peer" TCP/IP error on my server) and only the run #6 completes 100% (all 10,000 rows).

 

For comparison, if I invoke the same REST function with a web browser my logs show only one query (as expected) with a 100% transmission of the content without any TCPI/IP errors.

Anonymous
Not applicable

Did someone find the solution on this? I'm checking the redshift monitor and I've got the same problem, power BI executes exactly 3 times each query

Anonymous
Not applicable

So far I have not found a way around this. It is extremely anoying when doing large queries...

Anonymous
Not applicable

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 Smiley LOL 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.

Anonymous
Not applicable

What does your "Edit Queries" section look like.  Do you have 3 tables all running the same query?  If that was the case, i'd suggest having a hidden table to run the query once and then 3 other 'blank queries' that use that hidden table in its source line.

Anonymous
Not applicable

I ran this in a brand new file with only the single data source, no transforms, modified columns, or any other processes but to query the data. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors