Forum Discussion

zdehanzal's avatar
zdehanzal
Regular Visitor
3 years ago

Performance degradation with sequential query execution in direct query

Hello PoweBI experts, 

 

we are having issues with load time for one individual simple visual with direct query storage mode against Bigquery.

 

We have star schema as data model behind and even with a simple bar chart visualization over multiple fact tables (2 fact tables having two measures, shared dimension between as business units and calculated measure to get the difference ... as example)

we see multiple queries being sent to our datawarehouse being bigquery.

We see simple DAX statement in daxstudio but in query timings and on bigquery side we see actually multiple queries being sent to individual fact tables. That would not be a problem, the strange things is they are exectued in SEQUENCE!

Even if each of them takes few seconds (2-4 sec as we have hundreds of millions rows in those fact tables) if we summarize the execution time, due to sequential execution we get response to over 10s to load one simple visualization.

The only parallel load we see is when we have multiple visuals, than indeed those are loading/querying at the same moment and the final load time for the whole dashboard would be equal to the load time of the slowest visual.

 

Does someone experience the same, is it a known limit in the current version of PowerBI with direct query (being may 2022 release in our case)?

If there is no way to execute those sub-queries in parallel is there way to force the engine to do kind of query folding to send only one large query instead of multiple (doing the joins on data source instead of powerbi side), that should make the visualization load time faster with MPP datasource as bigquery behind .

 

Thank you 

Best Regards

Zdenek

 

 

Need Help

1 Reply

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi zdehanzal ,

     

    You can set the maximum number of connections DirectQuery opens for each underlying data source, which controls the number of queries concurrently sent to each data source.

    Increasing Maximum connections per data source ensures more queries, up to the maximum number specified, can be sent to the underlying data source. This approach is useful when many visuals are on a single page, or many users access a report at the same time. 

     

    For more information, please refer to:Using DirectQuery in Power BI - Power BI | Microsoft Learn

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.