cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
martinav
Super User
Super User

Faster way to join tables

I have a sharepoint list, and an SQL table.  I essentially want to do an SQL JOIN between them, on the PartNumber column.  I can do it, but its taking a long time to generate.  I was hoping for a quicker way to handle this.  Any ideas?

 

ClearCollect(ECN_Data_Filtered_PreLoad, Filter('Engineering Change Notice',  ECN_Status_PowerApps_Value=4));

ClearCollect(ECN_Data_Filtered, 
             AddColumns(ECN_Data_Filtered_PreLoad,
                        "Material",LookUp('[dbo].[VIEW_ALL_PDM_LATEST]',PartNumber=Part_x0020_Number).Material)),
                        "ProjectID", LookUp('[dbo].[VIEW_ALL_PDM_LATEST]',PartNumber=Part_x0020_Number).ProjectID))

 

 

The only thing I could think of would be to preload the other table into a collection first, but how can I filter it based on the same PartNumber first?  There are far too many records to do the full table.  I know operations with collections are far quicker than lookups over an on prem gateway to the SQL server.

1 ACCEPTED SOLUTION

Accepted Solutions
a-ovbord
Community Support
Community Support

Hi @martinav,

 

The data center region through which the Gateway is routed will show in the Status tab inside the app, as shown at point 6 at this link.

Also if you go to https://admin.powerapps.com/environments with an Admin user, you will see the regions for all your environments. Usually everything is in one region, the default one chosen when the tenant was created.

 

And so Power Apps and Flow will run on the environment you select, which is located in a particular region, as mentioned previously.

 

Hope this helps.

Thank you,

Ovidiu

View solution in original post

3 REPLIES 3
a-ovbord
Community Support
Community Support

Hi @martinav,

 

A great deal of impact in performance, when it comes to Data Gateways is having the gateway in the same region or close to the environment's region.

Reason being with distance comes latency.

Are they in the same region?

 

Thank you,

Ovidiu

@a-ovbord ,

 

Thanks for the reply.

 

How do I even know what region it is in?  My on prem gateway is local (Texas, USA).  How do I know what region everything else is in?  I'm not using azure.  I have no idea where Power Apps and Flow are running from.

a-ovbord
Community Support
Community Support

Hi @martinav,

 

The data center region through which the Gateway is routed will show in the Status tab inside the app, as shown at point 6 at this link.

Also if you go to https://admin.powerapps.com/environments with an Admin user, you will see the regions for all your environments. Usually everything is in one region, the default one chosen when the tenant was created.

 

And so Power Apps and Flow will run on the environment you select, which is located in a particular region, as mentioned previously.

 

Hope this helps.

Thank you,

Ovidiu

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,426)