cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shyamcharan
Helper I
Helper I

Performance issues - Loading, previewing, evaluating etc

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.

1 ACCEPTED SOLUTION
v-luwang-msft
Community Support
Community Support

HI @Shyamcharan ,

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.

vluwangmsft_0-1643187451331.png

 



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

View solution in original post

5 REPLIES 5
v-luwang-msft
Community Support
Community Support

HI @Shyamcharan ,

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.

vluwangmsft_0-1643187451331.png

 



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
Super User
Super User

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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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"

 

 

 

 

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_0-1642949765103.png

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




also share your pbix if you can.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors