Performance issues - Loading, previewing, evaluating etc

[Anonymous]

Hi Experts,

I am trying to build a very basic graph with three source tables which has less than 2 million rows in two tables and 2000 rows in third table.

I see a lot of performance issue loading the data to Power BI from the SQL Server database.

Loading, previewing and evaluating taking a lot of time or like runs forever.

What am I missing?

Appreciate your help.

[v-luwang-msft · Accepted solution]

HI @Anonymous ,

Here are some options to improve some performance that you can optimize based on what I have mentioned below.

1. Use the latest version of Power BI Desktop

2. Untick the following options in your current PBIX file. You can go to File -> Options and settings -> Options->CURRENT FILE(Data Load) to find the options.

3. Follow the guide in this blog to disable Load for these intermediate queries in your PBIX file to save memory.

4. Optimize your data model by following  the tips in the articles below.
  Data Import Best Practices in Power BI
  Power BI Performance Tips and Techniques

5. Use a faster computer with bigger memory would help in your scenario.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[vanessafvg]

how are you fetching the data from sql, can you post your m code?  is this direct query or imported?

do you know if you are using query folding?

how long is the load taking?

[Anonymous]

Thanks for the response venessa.

Hope the below is what you are after. No query folding. Also using import option.

The load takes over 1 hour.

let
    Source = Sql.Databases("Server001"),
    AISDM = Source{[Name="DB001"]}[Data],
    dbo_Activity_Master = AISDM{[Schema="dbo",Item="Activity_Master"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_CADM_Activity_Master, each [Date_Response] >= #datetime(2018, 1, 1, 0, 0, 0)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Date_Response"}, View_DIM_DateMap, {"Date"}, "View_DIM_DateMap", JoinKind.Inner)
in
    #"Merged Queries"

[vanessafvg]

ideally you want to achieve query folding, so that the compute of your sql server is used.

are both these tables on sql?  it would be better to join the in a sql query filter via a sql statement, the problem seems to me that you are using the mashup engine rather than the sql server compute because of your merge join.  That probably has broken the query folding.

better to write the full sql query and in sql whether via a view or in the advanced options area and filter your date from there.

[vanessafvg]

also share your pbix if you can.