Slow DirectQuery Report due to limited queries executed by Data gateway

Question

Hey, Genius&nbsp;As the Subject mentioned, i am suffering unknow reason slow reports response time.&nbsp;Below is the basic information and senario.&nbsp;License&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : free license(upgraded to Pro for evaluation)Report Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : DirectQueryQuery Detail&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : select from hundred thsuand table and group by to around 100 recordsSimutaneous Refresh : around 10 reports refreshing at same timeResponse Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : for longest around 1 minute( when refresh alone, around 6 second)&nbsp;Performance counter:&nbsp;number of queries executed: 1 ~ 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (the requests sent from Power BI were executed one by one, not simutanesouly)&nbsp;CPU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : 2~4%&nbsp;Memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :almost unchanged&nbsp;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?&nbsp;

xuetechpowerbi · Answer

Hey, Genius&nbsp;As the Subject mentioned, i am suffering unknow reason slow reports response time.&nbsp;Below is the basic information and senario.&nbsp;License&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : free license(upgraded to Pro for evaluation)Report Type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : DirectQueryQuery Detail&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : select from hundred thsuand table and group by to around 100 recordsSimutaneous Refresh : around 10 reports refreshing at same timeResponse Time&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : for longest around 1 minute( when refresh alone, around 6 second)&nbsp;Performance counter:&nbsp;number of queries executed: 1 ~ 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (the requests sent from Power BI were executed one by one, not simutanesouly)&nbsp;CPU&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : 2~4%&nbsp;Memory&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :almost unchanged&nbsp;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?

v-sihou-msft · Answer

XUETechPowerBI
&nbsp;
When refreshing dataset, a query is created by Cloud Service and&nbsp;send it to&nbsp;the queue for gateway to process, then push the requests&nbsp;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
&nbsp;
Regards,

xuetechpowerbi · Answer

v-sihou-msft&nbsp;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&nbsp;because this is how&nbsp;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).

Forum Discussion

Slow DirectQuery Report due to limited queries executed by Data gateway

3 Replies

Related content

DirectQuery limitations?

DirectQuery Limitation

Copilot, limitations?

Directquery Row limit

Data limit Issues