Poor Power BI Performance with Inner Join in MS Access tables (10 rows per minutes)

[aktripathi2506]

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.

[alm5084]

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.

[CahabaData]

in PBI Desktop one can join between tables, not queries as standalone objects in the sense that those are used in the database world. 

It was never quite clear/resolved why the original poster had a slow experience.  It can depend on the record count and strength of the computer but I don't see alot of complaints about slowness specifically due to joins.

still not quite clear your starting data set structure and what you seek, perhaps best to start new thread

[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.

[CahabaData]

You do not need to do a Merge Query.  Instead just do a join line between the 2 tables - the same as in a database.

Then in a measure/visual you can call the data from the 2 tables together using the RELATED or RELATEDTABLE function / method.

[aktripathi2506]

Hi @CahabaData,

Can you please give me an example or demo of it? THat How can be perform inner and left join by using this function after creating the relationship between the tables.

For example, suppose I have 2 tables:

Table 1
ID	Name
1	A
2
3	B

Table 2
ID	Sub	Score
1	Eng	22
2	Math	34
3	Physics	43
4	Chemistry	46

Now in table 2, I want to create a column which will look for the column name "Name" in table 2, if 'Name' is not empty then write NEW else write OLD in that column.

Based on this I will have to sum the score of the OLD and NEW student.

I can do this by using slicer and filter, but I have to do this by using formula, it is much needed.

[CahabaData]

Select the relationship view (left frame) and create a join line between the 2 tables on the ID field

Select the data view (left frame) - and for table 2 create a calculated column - found in the Modeling tab - - which I have called Status

Status = IF(LEN(RELATED(Table1[NAME]))>0,"New","Old")

[pqian]

@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. 

[aktripathi2506]

Hi @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]

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.