Forum Discussion

cjemmott's avatar
cjemmott
Icon for Advocate IV rankAdvocate IV
9 years ago

Direct Query Azure SQL Speed Problems

I am having some performance issues with Power BI, both Desktop and Premium with Azure SQL Direct Query.  I think it is related to inefficient queries being generated by PBI in some cases.   One ex...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi cjemmott,

 

According to your description above, I have tested it with the latest version of Power BI Desktop(2.48.4792.721 64-bit (July 2017)). And I cannot reproduce this issue on my environment.

 

I would suggest you try creating a support ticket on Power BI Support page for better assistance on this issue. :smileyhappy:

 

Regards

  • cjemmott's avatar
    cjemmott
    Icon for Advocate IV rankAdvocate IV
    9 years ago

    I opened a support ticket.  Here is some additional information:

     

    I ran the same query multiple times from PBI desktop, clicking “refresh” in between to clear the cache.  Timing for the long query on two of those were 33 and 26 seconds as viewed from Power BI.  Average time from Azure SQL Query Performance Insight was 17 seconds.

     

    This SQL database is using a pool with 800 eDTU and was the only thing running.  Multiple tables are joined, the smallest of which has 50 rows and 8 columns, and the largest 50k rows and 12 columns.  This seems like it should be very fast!

     

    But somehow the query is 1972 lines long!  It basically enumerates every possible case…

     

    I did the same exact query (well, full outer joins rather than relationships with referential integrity) from Tableau.  Same database, computer, etc.  Tableau used 12 lines of SQL, 2.31 second total query + render with no cache.