cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

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)

27 REPLIES 27
Highlighted
Community Champion
Community Champion

Re: Power BI runs query multiple times on backend?

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.

Highlighted
Advocate I
Advocate I

Re: Power BI runs query multiple times on backend?

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. 

Highlighted
Advocate I
Advocate I

Re: Power BI runs query multiple times on backend?

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

Highlighted
Advocate I
Advocate I

Re: Power BI runs query multiple times on backend?

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

Highlighted
Advocate I
Advocate I

Re: Power BI runs query multiple times on backend?

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.

Highlighted
Regular Visitor

Re: Power BI runs query multiple times on backend?

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.

Highlighted
Frequent Visitor

Re: Power BI runs query multiple times on backend?

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.

Highlighted
New Member

Re: Power BI runs query multiple times on backend?

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.

Highlighted
New Member

Re: Power BI runs query multiple times on backend?

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.

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021