Forum Discussion

alexrobe's avatar
alexrobe
Advocate III
9 years ago
Solved

MySQL Database access: What ist the most efficient way to minimize to database host load

Hi,   I am new to Database access via PowerBI, so please accept my apologies for raising a maybe newbie question.   I want to migrate my Excel imports currenly 7 files with production defect data...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    alexrobe,

    Based on my test, when merging tables in Power BI Desktop, Power BI will push the queries that select data from tables up and run it on the database server side.

    Except the settings that you have disabled in Power BI Desktop, please add Table.Buffer function in the codes of your advanced editor to reduce the load on the database server.

    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
9 years ago

alexrobe,

Based on my test, when merging tables in Power BI Desktop, Power BI will push the queries that select data from tables up and run it on the database server side.

Except the settings that you have disabled in Power BI Desktop, please add Table.Buffer function in the codes of your advanced editor to reduce the load on the database server.

Regards,
Lydia

  • alexrobe's avatar
    alexrobe
    Advocate III
    9 years ago

    Thank you Anonymous!

    It indeed seems to improve loading times significantly!
    Still I wonder where to put in this command? Currently I have it in the very first line only...

    eg. for my table "ts_assemblyunit":

     

     

    let
    Quelle = Table.Buffer(MySQL.Database("srvqm01", "iqm", [ReturnSingleDatabase=true, CreateNavigationProperties=false])),
    iqm_ts_assemblyunit = Quelle{[Schema="iqm",Item="ts_assemblyunit"]}[Data],
    #"Entfernte Spalten" = Table.RemoveColumns(iqm_ts_assemblyunit,{"DEPTH"})
in
    #"Entfernte Spalten"

     

    - would it also/more make sense at the End of the import?

    e.g.:

    #"Entfernte Spalten" = Table.Buffer(Table.RemoveColumns(iqm_ts_assemblyunit,{"DEPTH"}))

    - would you only recommend using Table.Buffer() for the imported tables or also for large tables based on the imported tables

     

     

     

    let
    Quelle = Table.Buffer(#"ts_assemblyunit"),
   ....
       lots of manipulation steps
       ... 
       final result = Table.Buffer(#'last step')
in
    final result