Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
nirvana_moksh
Impactful Individual
Impactful Individual

Union and Merging Slowing Performance Terribly

Hello All,

 

I have a report which is using 20 SQL views and I am doing few transformations on each, then disabling their load and then creating a UNION of all 20 (the business need is in such a way that I have to have a UNION in place), the data is very small in total and loads super fast (1 minute 20 seconds avg.) when it is only for the 20 views, when it comes to the UNION it takes around 29 minutes!!! I don't get why is it doing that, I added Table.Buffer on all views even though it was not needed and I am wondering what is causing this slow performance. Also, will Table.Buffer help prior to the Table.Combine in the UNION query?

 

Any help would be awesome!

 

Thank You

11 REPLIES 11
Anonymous
Not applicable

Are you importing the entire dataset and then making transformations? If yes, then it will give you slower performance. You can write a SQL query and import the transformed data. It will give you much faster results. 

I am refrencing each view individually and navigating to that view alone and then performing the needed transformations on each view.
quentin_vigne
Solution Sage
Solution Sage

Hi @nirvana_moksh

 

On PowerBI, are you using Import mode or Direct Query ? 

When I was using DirectQuery it was the problem, it was too slow. The solution was to import data 

 

- Quentin

I am using import mode for each of the 20
Views, no direct query for now.

@nirvana_moksh

 

Did you try not doing UNION and creating relation between tables on PowerBI ? 

 

Else, are you using every column on your 20 tables ? I had the same issue, and creating view of my table with only the needed columns and keeping data only after 2013 solved my problem

While troubleshooting this issue I did delete the UNION and the load was really quick but the UNION is definitely the issue, and I do need the UNION more so for making life easier when I do the row level security. Also, after this UNION process I merge it’s data with the Power Apps data as well in this model. So having the UNION is needed regardless.

could it be that the union is actually fast, but the merge with PowerApps data that you mentioned makes the whole process slow?
have you tried loading just the union data (withouth merge) to the model? if so, how fast is it?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

I have actually removed the Power Apps data and just kept the UNION but it still takes long. I am going to redo a few things today and see where it takes me.

Anonymous
Not applicable

I am having the same issue.  The union function has slowed down my model dramatically.  I am joining multiple tables that each calculates supervisor hierarchies at different points in time (path function).  Have you explored simply a limitation with your machine?  I noticed that my CPU usage and Memory (20 Gb) is maxed out the entire time.  Prior to adding the union function, my model would max out resources but get through jobs in a manageable amount of time.   I was wondering if anyone on this string has used virtual machines, with more power, to refresh their data models - could that be a solution?  (I am a complete newb to virtual machines and looking forward to seeing what community has to say).  I am hoping there is a model solution and not a "throw more memory at it" solution.  Thank you!  

So what I did do was change the M for each of the 20 views, instead of it navigating to full hierarchy of the Schema under which they were against, I referenced the view directly. I used to have VM memory issues and raising it helped for sure. I also unchecked the 'Allow data preview option...'1.PNG

Maybe you could try to import the views without union and do the relationship between table on powerbi.

You will do the RLS after that and it will still work

 

We don't know what your data looks like so advising can be difficult

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.