Forum Discussion

dilkushpatel's avatar
dilkushpatel
Icon for Helper III rankHelper 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 qu...
Need Help
Show and Tell
Tips and Tricks
fbrossard's avatar
fbrossard
Icon for Kudo Commander rankKudo 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
Icon for Continued Contributor rankContinued 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
    Icon for Resident Rockstar rankResident 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.

    • fbrossard's avatar
      fbrossard
      Icon for Kudo Commander rankKudo Commander
      10 years ago

      agree with greggyb live connect on Azure SQL or SQL Server On-Prem wil be slower than querying a powerpivot model fully compressed and using columnstore index. The only way to get closer to this kind of performance is using columnstore indexes on your SQL DB. But, even if you use CCSI on SQL DBs, Power BI traduce DAX query into SQL query which are not well optimized for SQL.

      That's the same if you build an SSAS Taular Model, you will have better performance if you use In-Memory mode compared to  Direct Query.

      But if you use an APS (aka PDW) or an Azure SQL Db as source, you could have better performance, because you will benefits from CCSI and MPP.

       

       

      • andre's avatar
        andre
        Icon for Memorable Member rankMemorable Member
        10 years ago

        these are some of the things to consider as you weigh in your options regarding direct query and import modes:

        • Generally speaking, import models will be faster, regardless of how powerful your database/appliance is, unless all of the measures are materialized, but even then, there will be network latency to consider
        • Direct query models cannot yet currently be enhanced with calculations
        • Neither direct query nor import based models solve the security issue until RLS is implemented in the service
        • Direct Query based models allow us to work with datasets that cannot be imported into a model that's less than 250MB which is the maximum size supported by the service right now

        In short, as of right now, unless you are dealing with too much data to load in your Power BI model, I would always recommend to rule out the import option first.