Forum Discussion
PowerQuery: Merge Queries HORRIBLE performance
hi, Anonymous
here are some suggestions for you refer to
1. When you connect to the SQL Server you can expand the "Advanced options" and input a SQL query to pull in the data that you want.
https://community.powerbi.com/t5/Desktop/Custom-SQL-instead-of-table-or-view/td-p/96463
2. Use directquery instead of import, do these steps in SQL
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
3. "Removed Duplicates" as this blog
Best Regards,
Lin
Hi Lin,
I actually tried the ideas in Chris Webb's blog already - "tricking" Power Query into knowing what the primary key was didn't help performance at all.
Note that the merge isn't a problem "after" the columns from the 2nd table have been expanded, but the step that actually does the merge itself (BEFORE you can expand the columns) is. Power Query seems to import the entire tables for some reason...no idea why.
Also, we're trying not to ask end users to have to write SQL - one because some of them can't do that...but also because if you use a SQL statement but then have to do any processing in Power Query to do anything else, it can't push down, causing huge performance issues.
I have not tried the "use direct query" option yet - we import everything to get the performance of in-memory. But maybe I could start with direct queries and then flip them to import - I'll give these a try and see if, in direct query mode, this issue goes away. It wouldn't be a perfect solution (for example, if we needed to later change the ETL then there is no way to go from "import" back to "direct query" - but at least it would be something.
Thank you!
Scott
p.s. when I say "Note that the merge isn't a problem "after" the columns from the 2nd table have been expanded, but the step that actually does the merge itself (BEFORE you can expand the columns) is. Power Query seems to import the entire tables for some reason...no idea why"
what I mean is - I don't understand why power query has to pull in the entire tables...if it just needs to list the columns so you can use the "expand columns" function, then it should (at most) only need to bring back a single row. Actually it "shouldn't" need to do this - Power Query already has the list of columns from the merged table before it even starts the merge. But if for some reason this step needs to show them, then instead of joining locally in PBI (which it seems to be doing), it should do a "select top 1 * from a join b" type of thing.
I'd be happy to do a webex to demonstrate the exact issue if that would help - we'd love to get this resolved. We've got users trying to join tables with tens to hundreds of millions of rows in...and this bug grinds development to a halt.
Thanks!
Scott