SQL Server input - Is it more efficient to make your own SQL call or let Query folding do it?

[NordicDrow]

Hello there!

So, a bit of a specific thing. As im dealing with huge data bases. As many might know, when you use an SQL input, all the first appliable steps in PowerQuery will be "folded" into a SQL call so to optimize the query. Otherwise, you can create a manual SQL call, which has the benefit of having all SQL functionalities (such as SELECT DISTINCT, which wont work as query folding for Remove duplicates).

But, my question is, for the same process, lets say, just filtering by several conditions. Is there any benefit in doing a personal SQL call? Or is it better to just make normal steps and let it create its own native query call?

Im talking either about efficiency or good/standard use.

Im making my own tests, not sure yet, the native query created by PQuery is very ugly, but seems to work the same.

[mattiasdesmet · Accepted solution]

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