Forum Discussion
Helper IVPoor Power BI Performance with Inner Join in MS Access tables (10 rows per minutes)
Hi,
My intention is to inner join two tables from MS Access database.
When I do left or right join then it work ok but whenever I merge tables with inner then it is very very slow.
Just want to know is this a common issue or only I am having this issue.
Any possible solution to enhance the process like rather than selecting "Merge Query" from UI, should I program it or what.
Please advise.
Thanks.
13 Replies
pqianMicrosoft Employee
aktripathi2506, It's interesting that only inner join is slow. Are you by any chance joining on non-key columns? Also, if you right click on the merged table step, is "View Native Query" enabled? (If so, the query is been executed on the source, otherwise, there maybe an inefficiency that the mashup engine is performing the join locally)
Depending on your scenario, you can also try loading the tables without joining them, and then define a relationship in the reports view.
aktripathi2506Hi pqian, Thank you for the reply.
"View Native Query" is disabled. Although its good to know that otherwise it will excute on source. Thanks.
If we define only relation between two tables then it doesn't allow us to use other tables column in the formula.
For example if Table 1 has: Id, date, hours, task
Table 2 has: ID, name, salary.Then if I want to create a column in table 1 which needs to use salary then I can not do that. This is just an example. I have a situation where I need to use the column from the 2nd table in formula.
I have 5-6 tables which I need to merge based on different join. Basically I am trying to convert the SQL query result in power BI.
- CahabaData
Memorable Member
I cannot explain the slowness but it is not clear to me why one would merge related tables, particularly should there be a 1:Many relationship. Making the join line is sufficient for the need to find data that is in both tables in a calculated measure when used with the RELATEDTABLE method/function. One of the benefits of PowerBI is not needing to flatten relational tables.
- aktripathi2506
No opinion or similar situation with anyone.
I checked multiple times, everytime for left or right join it work fast but for inner join it workes very slow I am connecting with Access database and both the tables has around 10 columns and 500,000 and 1,200,700 rows respectively.
- CahabaData
I cannot say why the inner join would differ than the outer in terms of speed. That remains a mystery. My question is that I do not understand what is slow? A merge query? I don't see a need to do a merge query when using Power BI so that would avoid this slowness perhaps.
Are these two tables importing/imported into Power BI?
- aktripathi2506
I meant: When I do merge query then merging the query in query editor is slow.
at bottom right corner it update how many rows it has loaded so that is how I know it is loading very slow because I have in total millions of rows.
And also when the query get complete in Query editor mode and when we select load and apply, at that time also it takes a lot of time.
For the 2nd question: Why do I need to merge the query:
Like I mentioned in my last reply that I am trying to get the result of a SQL query so that is why I was connecting the tables in the same way. I need a column which can use columns from other tables, I can not do all calculation based on measure.
I wish I could share the data with you but I can not and also creating a sample test data will be difficult.
Power BI has merge query option so I hope they must be having some need behind it.
All the tables are imported from access data base into power BI.
- alm5084
Helper II
Old post I know, but I am also having trouble with this. I don't want to load ALL the data into multiple tables from my datasources. I have about 6 queries from various databases/datawarehouses that all need the same 7 values in a dimension selected, e.g. "term1" "term2" ...."term7". I create one query that is a list of these 7 values, and then I want to inner join to my other queries to limit the rows they return to only include those 7 items. This way, I can change all the data in the whole data set by updating only one query. I need to do this several times a year for every single report, and as it stands now I cannot merge with inner join so instead I must change these filters in every single query in every single report. If the inner join merge worked I could simply change one of the queries and update everything. This is a legitimate use case, and saying "you can do something else instead" doesn't fix the issue.
Thread bump.
- CahabaData
It's not clear if your post is really a continuation of the original thread. It doesn't sound like your data is stored in Microsoft Access.
But in any case I don't quite follow what is your situation in PBI Desktop; as I understand you have multiple different databases and you'll import data I presume.
Depending on the brand of database one can make a View in that database so that the data set available to PBI is just the data you seek and not the entire table of the database.
- alm5084
Then I would have to create views for every one of my queries in almost every dataset, as they are all somewhat different.
