Answered

Faster way to join tables

(0) Share

Report

Posted on by martinav

3,347

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.

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

a-ovbord Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

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

a-ovbord Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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