Slow performance joining SQL Server table and Excel table

[Anonymous]

Hi folks,

I have a doubt about the behaviour of Power Query (using in Excel 365).

Scenario: 

• I import a table from an Excel file (~10k rows);

• I link to an SQL Server database (~3mm rows) - I'm not doing "select from table", instead i'm selecting the table in list (i read or view in someplace i don't remember that is the "correct way"?)

• In some moment, i make an inner join between the two tables (Excel and SQL Server);

In this last step, I noted that Power Query "download" the entire SQL Server table (~3mm rows) to make the join. For me, this makes sense and at same time does not.

I have permission to write what I need in the SQL Server, but I was trying to do entirely in Power Query, but the processing time doing on it is unacetable.

Is there any recomendation to improve performance doing it at the "best practices" or is this the expected behavior?

Best regards.

Orlando

[lbendlin]

Also check the performance difference when you do the merge "the other way round". Instead of merging the SQL table to the Excel table, merge the Excel table to the SQL table.  Play with Table.AddColumn instead of Table.NestedJoin etc.

[ImkeF · Accepted solution]

Hi @Anonymous ,

yes that's a know issue. Query folding will not happen in your case.

But there's a workaround: Solved: Parameterized SQL Query with query folding - Microsoft Power BI Community

More details about the reasons: SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel – The BIccountant