cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
XUETechPowerBI Frequent Visitor
Frequent Visitor

Slow DirectQuery Report due to limited queries executed by Data gateway

Hey, Genius

 As the Subject mentioned, i am suffering unknow reason slow reports response time.

 Below is the basic information and senario.

 

License                       : free license(upgraded to Pro for evaluation)

Report Type               : DirectQuery

Query Detail               : select from hundred thsuand table and group by to around 100 records

Simutaneous Refresh : around 10 reports refreshing at same time

Response Time          : for longest around 1 minute( when refresh alone, around 6 second)

 

Performance counter:

 number of queries executed: 1 ~ 2

                                               (the requests sent from Power BI were executed one by one, not simutanesouly)

 CPU                                      : 2~4%

 Memory                               :almost unchanged

 

So what is problem for data gateway not executed request simutaneously????????

I have searched around for a while but no clues at all. And I see the Number of Queries Exected on some site shows hundreds.

May the license caused this phenomenon?

 
3 REPLIES 3
XUETechPowerBI Frequent Visitor
Frequent Visitor

Slow DirectQuery Report due to limited queries executed by Data gateway

Hey, Genius

 As the Subject mentioned, i am suffering unknow reason slow reports response time.

 Below is the basic information and senario.

 

License                       : free license(upgraded to Pro for evaluation)

Report Type               : DirectQuery

Query Detail               : select from hundred thsuand table and group by to around 100 records

Simutaneous Refresh : around 10 reports refreshing at same time

Response Time          : for longest around 1 minute( when refresh alone, around 6 second)

 

Performance counter:

 number of queries executed: 1 ~ 2

                                               (the requests sent from Power BI were executed one by one, not simutanesouly)

 CPU                                      : 2~4%

 Memory                               :almost unchanged

 

So what is problem for data gateway not executed request simutaneously????????

I have searched around for a while but no clues at all. And I see the Number of Queries Exected on some site shows hundreds.

May the license caused this phenomenon?

Moderator v-sihou-msft
Moderator

Re: Slow DirectQuery Report due to limited queries executed by Data gateway

@XUETechPowerBI

 

When refreshing dataset, a query is created by Cloud Service and send it to the queue for gateway to process, then push the requests to Azure Service Bus. So it will resolve the request one by one, not simutanesouly. For more details, please see: On-premises data gateway in-depth

 

Regards,

XUETechPowerBI Frequent Visitor
Frequent Visitor

Re: Slow DirectQuery Report due to limited queries executed by Data gateway

@v-sihou-msft Thanks for replay, after some tests, i found the reason of slow report is at DB side thourgh Data gateway seems process request one by one.

You pointed out the request is resolved one by one because this is how Azure Service Bus's queue works wihch makes me wondering the potential maximum queue size and how many requests data gateway fetches per second.( The log seems data gateway changes fetching frequency dynamically).