Forum Discussion

dilkushpatel's avatar
dilkushpatel
Helper III
10 years ago

Performance issue with Direct Query with SQL Azure

Hi,

I'm trying to use direct query feature with data from SQL Azure DB.

Now I do have quite a good amount of data in there . (one of the fact has 7 mil records)

 

I understand that with qirect query it will try to do query for each and every click and importing this amount of data also does not make sense.

 

So is there any way I can improve performance so that i can create viable report?

 

This could well be more of a Azure question but wanted to see if there is anything in power bi that can be helpfull.

 

just to give perspective simple select count from fact table with 7 mil records took 35 min.

 

Any tips for improving perfomance?

 

Thanks,

Dilkush

Need Help
Show and Tell
Tips and Tricks

13 Replies

  • andre's avatar
    andre
    Memorable Member
    10 years ago

    there are three things you can do:

    1. create an index and see if that helps
    2. bump up the performance characteristics of your Azure SQL Database
    3. explore in-memory option for your database

    7M rows is not a large dataset and if your tables are not very wide, I would also explore importing the entire data set into your model, my guess you will see good compression with that and it might be below the 250mb limit

    • dilkushpatel's avatar
      dilkushpatel
      Helper III
      10 years ago

      Hi,

      By In-memory option for database you mean importing in pbix or SQL Azure has some setting for this?

      Actiually there are 3 tables with approx 7 mil each and then there are 5 dimension tables.

       

      I will try importing in pbix and see how it goes.

       

      Thanks,

      Dilkush

      • andre's avatar
        andre
        Memorable Member
        10 years ago

        yes, in Azure SQL you can create in memory indexes which can potentially improve performance.

  • fbrossard's avatar
    fbrossard
    Kudo Commander
    10 years ago

    If your Azure SQL DB is at Premium Level, you can use Clustered Culomn Store indexes.

    But andre is right 7M rows is not large.

    Do you need live connection on your Azure SQL DB ? 

    If not, try to use PBI Desktop to import your data. I've worked with a 4 milions rows fact table, and my pbix size was less than 50 Mb.

     

    • Bjoern's avatar
      Bjoern
      Continued Contributor
      10 years ago

      fbrossard

       

      Live-Connectt should be faster. Live-Connect will always be more favorable than an import, as security-settings, scalability ... blabla. Lot of reasons.

       

      I would be interested whether the ccsi have solved the issue. 

      • greggyb's avatar
        greggyb
        Resident Rockstar
        10 years ago

        Bjoern, it seems to me that the expectation should be that any DirectQuery will be slower than a hosted model. You get an extra round-trip of latency from Power BI Service to SQL Server, along with potential bandwidth issues if the returned dataset is large (shouldn't be the case).

         

        Additionally, you've got an additional level of boilerplate query generation. DAX must be translated to SQL. Being two layers removed from writing optimized queries against the source DB leaves me expecting inefficient idioms in the queries, or at least sub-optimal.

         

        The potential upside is parallelism from the SQL query engine, and custom indexing.