cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HansHeintz
Level 8

Simple lookup in an addcolumns slowing all down disproportionally

My gallery is showing items of an entity that are first collected in a 

Clearcollect(collection1,filter(mytable,somefilter))

Then collection2 is made from collection1 by adding columns

Clearcollect(collection2,addcolums(collection1,'col1',syntax1,'col2',syntax2))
Collection2 is then the items property of the gallery.

This all goes well and fast until syntax1 is a simple lookup of an existing column value in an sql table
LookUp('[dbo].[dier]_1';identificatie_nummer_asiel=DierId;naam)
Collection2 is already down to 10 to 20 items, still it takes 5 to 10 seconds to process, thats 1/2 a second per lookup.

Is this expected performance?

If I use the sql table as an items property to a gallery it doesn't seem particularly slow.

 

1 ACCEPTED SOLUTION

Accepted Solutions
K-A-R-L
Level 10

Re: Simple lookup in an addcolumns slowing all down disproportionally

Hi @HansHeintz ,

When you LookUp a column can you please try using an SQL View instead.

You can also try to use Concurrent() on the AppStart Property of your app and create a collection of your SQL Views.

Thanks,
K-A-R-L


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

3 REPLIES 3
K-A-R-L
Level 10

Re: Simple lookup in an addcolumns slowing all down disproportionally

Hi @HansHeintz ,

When you LookUp a column can you please try using an SQL View instead.

You can also try to use Concurrent() on the AppStart Property of your app and create a collection of your SQL Views.

Thanks,
K-A-R-L


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

Community Support Team
Community Support Team

Re: Simple lookup in an addcolumns slowing all down disproportionally

HI @HansHeintz ,

Based on the issue that you mentioned, I think it is normal behaviour with the LookUp formula applied to the  SQL Table.

 

When the LookUp formula is executed, it would iterate all items stored in your SQL Table, and then find the first record which matched the filter condition you specificed.

In order to improve the LookUp formula execution performance within your app, you could consider add a Index to the corresponding Search column (e.g. 'identificatie_nummer_asiel') within your SQL Table. The Index could speed up the data retrieval within your SQL Table.

 

As another solution, you could also consider save your SQL Table records into a collection (called "SQLTableRecords") within your app, then use the collection as data source within your LookUp formula instead of the Original SQL Table data source.

I have made a test on my side, please consider take a try with the following workaround:

Set the OnStart property of the App control to following:

ClearCollect(SQLTableRecords; '[dbo].[dier]_1');

then modify your LookUp formula as below:

LookUp(SQLTableRecords; identificatie_nummer_asiel=DierId; naam)  /* <-- for your syntax1 */

please re-load your app, then check if the issue is solved.

 

You could also consider put your above two ClearCollect formula within the OnStart property of the App control. Set the OnStart property of the App control to following:

Concurrent(
ClearCollect(SQLTableRecords, '[dbo].[dier]_1'),
ClearCollect(collection1, filter(mytable, somefilter))
);
ClearCollect(
collection2,
addcolums(
collection1,
'col1', LookUp(SQLTableRecords, identificatie_nummer_asiel = DierId, naam),
'col2', syntax2
)
)

then set the Items property of the Gallery to following:

collection2

Please take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HansHeintz
Level 8

Re: Simple lookup in an addcolumns slowing all down disproportionally

There is an index on the column already. I am already loading tables in collections but this one has 40000 records so might be a bit of a slow down. Meanwhile i saw some youtube on sql view and that looks promising. Also the concurrent i was not using yet so that is a nice tip. I decided to load the last 1000 animals from the table in a collection for now which does the job of finding a name for views on the here and now most of the time.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,984)