cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PBIUWO
Resolver I
Resolver I

Temporary storing data efficiently, Set vs Collection?

Hi,

 

Problem:

I created a barcode scanning mobile app that does on each OnScan(), 2 to 3 Lookups() from the Barcode Scanned Value directly to a SQL database. On each scan it take up to 5 seconds to populate on the gallery. 

 

It is slow because the SQL database is large. 

 

Question:

To solve for this problem, I want to temporary store the Lookup() result (record). 

It will be 1 row of data that will clear after each scan. 

 

I'm wondering which of Set() or Collection() is more efficient when retrieving data? 

 

Set: 

Set(TEMP, Lookup(SQL, COLUMN = BARCODE.Value))

 

Collection:

ClearCollect(TEMP, Filter(SQL, COLUMN = BARCODE.Value))



The temporary stored data is then Collected to a separate Collection. 

With Set() I can use .ColumnName whereas, ClearCollect() has Lookup(TEMP...., ColumnName)

Ex. {ITEM: TEMP.ITEM}  

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@PBIUWO 

You can assign to either a collection or a variable either way.  Both are identical.  The only real difference with a Collection is that it has the ability to add or remove records to it easily.  

As for the variable, it can be anything you want - a record, a table, text, number, boolean, etc.

 

In your case I would avoid the collection as it does not seem that you are needing the ability to add or remove records to the table.

 

To be clear though:

    Set(TEMP, LookUp(SQL, Columns = Barcode.Value))

Will result in a variable called TEMP that will have the entire record of your SQL lookup.

 

Using a collection for this:

    ClearCollect(TEMPcol, LookUp(SQL, COLUMN=Barcode.Value))

Will produce a collection that has a table and one column called Value and will have a record in it that you looked up.

 

You can interchange these in any way that you want.

For example, 

   Set(TEMP, Filter(SQL, COLUMN = BARCODE.Value))

Will give you a variable with a Table in it that has records identical to the SQL datasource schema.

 

So, bottom line, if you need the ability to add or remove a record, then a collection is good.

If not, then stick with a variable as it will have less overhead for your app and is easier to troubleshoot.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@PBIUWO 

You can assign to either a collection or a variable either way.  Both are identical.  The only real difference with a Collection is that it has the ability to add or remove records to it easily.  

As for the variable, it can be anything you want - a record, a table, text, number, boolean, etc.

 

In your case I would avoid the collection as it does not seem that you are needing the ability to add or remove records to the table.

 

To be clear though:

    Set(TEMP, LookUp(SQL, Columns = Barcode.Value))

Will result in a variable called TEMP that will have the entire record of your SQL lookup.

 

Using a collection for this:

    ClearCollect(TEMPcol, LookUp(SQL, COLUMN=Barcode.Value))

Will produce a collection that has a table and one column called Value and will have a record in it that you looked up.

 

You can interchange these in any way that you want.

For example, 

   Set(TEMP, Filter(SQL, COLUMN = BARCODE.Value))

Will give you a variable with a Table in it that has records identical to the SQL datasource schema.

 

So, bottom line, if you need the ability to add or remove a record, then a collection is good.

If not, then stick with a variable as it will have less overhead for your app and is easier to troubleshoot.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

@RandyHayes 

 

I tested it with both and you're right. 

I used Set and was able to temporary store the data. 

With my app, I have gotten a few comments about bugs that appear with the weak internet connection from the users. The app does weird things without giving errors  or completing the full function. Hope having a lighter work load on the app helps

 

Thank you RandyHayes

RandyHayes
Super User
Super User

@PBIUWO 

Yes, having a weak connection will cause issues if the app is not designed for that.  When we have to design for poor internet connection, the app takes a dramatic change.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,047)