ShowColumns very slow when retrieving just a few rows

[Greg_N]

I am trying to retrieve a very small amount of data from Sharepoint into a collection, but the retrieval is extraordinarily slow.

I have a Sharepoint list that holds a number of integer columns, and a "multiple lines of text" column (called "Image") , which I am using to store image data. Here is a snippet:

The table currently has 49 rows (i.e. not many rows)

In part of my code, I wish to retrieve only the two integer columns shown above. My code is as follows:

This code takes around 45 seconds to run, and returns only the required 49 rows, which is exceptionally slow.

It appears that PowerApps retrieves every column in the table - which in this instance includes some large chunks of data in the "image" column, which takes a while - before then filtering out the two columns that I need.

Is there a faster way to do this? Is there a way to force PowerApps to genuinely only retrieve the two columns that I need?

Any suggestions gratefully received!

[timl]

Hi @Greg_N 

If you were using Dataverse or SQL Server as a data source, you can enable the "explicit column selection" setting to force Power Apps to retrieve only the 2 columns that you specify in your call to ShowColumns.

SharePoint however doesn't support this feature, so there's no way to retrieve only those 2 columns.

What I recommend in this scenario is not to store your images as base64 encoded text in a text column, but to store them in an actual SharePoint image column. You'll see an improvement in performance if were to do this.

[FLMike · Accepted solution]

Hi @Greg_N 

I would suggest that if this is a primary feature you need that you actually separate the Meta-Data about the Image from the Image And use your Primary Id as a Text and or Numeric Link between the two lists, (not a look up bleh look ups).

Now, you can also turn on Explicit as @timl mentioned but as they also mentioned it won't help here, so separating them will give you the biggest performance increase.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Greg_N]

Hi @timl  and @FLMike ,

Thank you both for your very prompt and helpful suggestions. Both look like good solutions.

Michael - prior to your response, I had considered this approach, but I wondered if I was being over complex, as I would then have to maintain and synch two tables. However, now having had the idea re-enforced through your suggestion, I feel that it is a perfectly viable option.

Nevertheless, I would like to try having all the data in one table - so my first approach will be to try out timl's suggestion of changing the column type, as this keeps the table simple. If I still have performance problems, I will revert to Michael's suggestion.

I really appreciate the rapid and useful feedback from both of you on this problem. Thank you.

[timl]

Thanks @Greg_N - good luck with this.

[FLMike]

Hi @Greg_N 

I am not sure what you mean by sync tables? Whatever works for you works, but its not complex

Table A with everything but the image and has a TableB_ID Column to write the row id from the item creation.

Table B has the image

Stops pulling data you do not need, especially if you ever need to load lists etc, plus the looks up are fast

But again do as you seem fit, but if you are going to do that mark @timl  's answer as the answer

[Greg_N]

For various reasons, I have ended up using @FLMike 's solution - just due to the way the images are currently handled in the code. And, as expected, it has made a big difference. That's not to say @timl 's solution wouldn't work - I just haven't had chance to try it yet.

Thanks again.