Forum Discussion

Tejinder's avatar
Tejinder
Helper I
2 years ago
Solved

Does simplifiying my SQL query from Dataflows reduces load on the soruce system ?

I am working on a project where I have to pull 20+ million rows of data from a remote SQL server, It was configured through dataflow. but it's been causing high CPU usage and crashes on the server, ...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    Instead of performing multiple joins and If statements, would it help if I do that once my data is in the dataflow?

    Performing merges in Power Query is extremely costly. Usually the SQL query engine is much (much) better at that.  The best merge/join is the one you don't have to do until the data is in the in-memory Vertipaq engine.  Let the data model do the work for you.

     

    Dataflows are there to shield you (the developer) from slow source systems.  If your SQL server is not slow, then ditch the dataflows and use datasets with incremental refresh instead.

lbendlin's avatar
lbendlin
Super User
2 years ago
Instead of performing multiple joins and If statements, would it help if I do that once my data is in the dataflow?

Performing merges in Power Query is extremely costly. Usually the SQL query engine is much (much) better at that.  The best merge/join is the one you don't have to do until the data is in the in-memory Vertipaq engine.  Let the data model do the work for you.

 

Dataflows are there to shield you (the developer) from slow source systems.  If your SQL server is not slow, then ditch the dataflows and use datasets with incremental refresh instead.