SQL Server Performance in Power Query

[DMiradakis]

Greetings everyone. I have an interesting question to ask regarding adding a SQL Server datasource in Power Query.

If I'm doing a basic SELECT statement in SQL, would you say it is typically best practice to make the query into a stored procedure rather than rely on Power Query to conduct query folding?

Note: I am talking about Import Mode only, so I'm not worried about Direct Query or Live Connection in the context of this question.

The reason I ask is because the concept of query folding in Power Query makes me think that Power Query has its own version of the SQL Server query optimizer inside the Power Query engine; however, I am more familiar with the SQL Server query optimizer, and I can troubleshoot it much easier than I feel that I can in Power Query.

To me, it almost seems to be a matter of choice: which query optimizer should you go with? My immediate thoughts are to make the query into a stored procedure and utilize SQL Server's query optimzer, rather than rely on Power Query to "fold" a native SQL query. In SQL Server, for instance, I can actually see execution plans, recalculate table statistics, and use SQL hints if necessary, unlike in Power Query.

Does anyone have any thoughts on this?

Best Regards,

Daniel

[lbendlin · Accepted solution]

I would generally agree with your assessment. If you know your data and your queries well then you want to keep control by disabling query folding.  (You didn't mention indexes which are a crucial part of the equation).

The SQL code generator used by Power Query' query folding is ok, but by design has to be overly verbose. So it is seldom optimal. 

You will also want to distinguish between import mode and direct query usage modes.  In import mode you can be a bit more lenient and let query folding do its thing, but in direct query mode you better have a well optimized set of indexes and a frequent check of the query plans.

[DMiradakis]

Awesome, glad to know that I was on the correct line of thought. I actually tested this late last week, and a slow SQL Sever query that I had added to Power Query was tremendously more performant once I added it as a stored procedure in SQL Server instead and then called that from Power Query.

Thanks!

[lbendlin]

That performance improvement likely came from the saved query execution plan.  You will want to check from time to time that the plan is still optimal for your data demographics.  What works well for 100K rows may not work well for 1M rows, or for changes in cardinality.

[DMiradakis]

Yeah I verified that the query produces a good execution plan, which I am very glad to see now. And thanks for the tip, I agree completely! As a general rule of thumb now, once I learned about parameter sniffing a few years ago, I typically mask my stored procedure parameters inside local variables in the stored procedure to account for that growth.

Thanks for the feedback!