Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

OData performance issues

We have OData endpoint where every record contains also curve data (array of {x: number, y:number} objects). Those curves have ~600 points and there are arround 30 curves per record. In other words, it's quite a lot of data. Anyway the performance is still much worse than expected.

 

Example data set: 40 MB.

Executing query with "curl":  ~11s (actual performance of backend)

 

For demonstation purposes I removed all visuals and replaced it with just getting count of the top level records.

Power BI (using pre-downloaded data as JSON source): ~21s

Power BI (using OData feed): 58s

 

Summary:

  1. Bottlneck of request processing is on the client side (Power BI) - our backend is far from being fully utilized during query execution.
  2. Using OData feed is significantly slower than manualy downloading JSON data and using them as a data source.
  3. Power BI (query editor, report refresh) is extremly slow even if the data are not transformed nor visualized in any way.

 

Is this normal behavior of Power BI?

What would you sugest to improve the performance?

 

My only idea is to remove curve data from data set and replace them with direct query where only a subset of the data will be visible at once. Or use different tool.

 

Any ideas will be appreciated.

Need Help

9 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-yuta-msft

       

      Thanks for reply, but unfortunatelly it's not helpfull at all.

      I can use google myself and I already read those before I posted this question.

       

      I'm looking for an answer to question why OData feed is much slower then downloading the data manually and using them as JSON source. Is it doing some validation against model which is time consuming or what?

       

      Anyway even then the performance is terrible. Somebody with experience could tell if it's even worth a try to optimize this. Is our approach - download all and filter in Power BI, wrong for our data model? Is direct query - where we will filter data serverside based on user selected parameters better approach for this case?

       

      Regards,

      Michal

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi, 

         

         

         

        Have you find an answer to the problem? 

         

        I have same issue with OData it is just terrible! 

  • trick73's avatar
    trick73
    New Member
    2 years ago

    Hi,

    I'm experiencing same disappointment with OData in PowerBI.

     

    OData involves pagination by design, so many requestes have to be issued when dealing with "big" data sets.

    My OData server has a default of 1000 rows per page. Increasing the page size on the server side has given some benefit but still it remains twice as slow in the average compared to a massive json download.

     

    I have written a small Python script to download the same OData feed and the performance gets sensitively better though still worse than the best case.

    So I suspect than beyond pagination Power BI does something else that makes it much slower.

     

    Some reference numbers:

    - 1.7 million rows dataset

    - 5.5 minutes with Power BI OData Feed

    - 3 minutes with Python OData Feed

    - 2 minutes one-shot json Download

     

    Regards

     

    Riccardo