Forum Discussion
SQL Server input - Is it more efficient to make your own SQL call or let Query folding do it?
The goal of query folding is to try and push as much of the query to the data source, so it can be calculated in the original datasource. Typically this engine knows best how to handle the request in most performant way. Only the steps that can't be folded are then executed in Powr Query, which is usually less optimal.
Writing a your own query is typically a good practice if you are proficient and knowledgeable about the source system and know the data model, which functions there are available, etc... in addition, a query optimizer from e.g. a relational database engine is extremely good in knowing how to best handle the query you ask it, bars on the statistics, indexes, and many other metadata available. So chances are that if you can write it in a single query for the data source, that gets processed the most efficient. some operations you'll need to do in power query (e.g. detecting patterns, and other advanced services that are maybe not available in the source).
https://learn.microsoft.com/en-us/power-query/query-folding-basics
The goal of query folding is to try and push as much of the query to the data source, so it can be calculated in the original datasource. Typically this engine knows best how to handle the request in most performant way. Only the steps that can't be folded are then executed in Powr Query, which is usually less optimal.
Writing a your own query is typically a good practice if you are proficient and knowledgeable about the source system and know the data model, which functions there are available, etc... in addition, a query optimizer from e.g. a relational database engine is extremely good in knowing how to best handle the query you ask it, bars on the statistics, indexes, and many other metadata available. So chances are that if you can write it in a single query for the data source, that gets processed the most efficient. some operations you'll need to do in power query (e.g. detecting patterns, and other advanced services that are maybe not available in the source).
https://learn.microsoft.com/en-us/power-query/query-folding-basics