Excluding Columns in the RESPONSE when querying database (Sharepoint)

[LeoGS]

[the other topic I created - exactly like this one - was somehow marked as SPAM. So I am recreating it]

Dear community,

I am currently running into user experience issues in one of the apps that I built.

This app has its databases in Sharepoint. One database specifically has three attributes/ columns named "Picture_1", "Picture_2" and "Picture_3", where I store photos made with the camera in base64 format (that, as you know, are HUGE).

In the beginning my users were having no issues, but as the app's usage grows, the number of pictures in the database also grows and the query in one screen (where I query the database to list the entries in a gallery - I do NOT show pictures here) is get REALLY slow and eating up this mobile data.

I analyzed it with the PowerApp Monitor and saw that in deed, the query has a response with over 150 MB and it takes (with a LAN Network) about a minute to finalize:

"duration": 60731,

"size": 150132299

If the people are walking around with LTE, it will take even more (3-5 minutes).

I changed the formula where I make the query from:

With(
{ _preFilter: Filter(dbo_AuditActions, Site = ComboBox_sites_1.Selected.SiteCode)
},
Sort(
Filter(
_preFilter, [... more code here...] )))

To:

With(
{ _preFilter: Filter(DropColumns(dbo_AuditActions,"Picture","Picture_2", "Picture_3"), Site = ComboBox_sites_1.Selected.SiteCode)
},
Sort(
Filter(
_preFilter, [... more code here...] )))

With the hope that "DropColumns" would delegate to the Database and say "hey, do NOT give me these columns". Unfortunately the result is not how expected, because it still returns the whole Database and THEN drop the columns.

Does anybody know how I can solve this without having to create a separated database for the pictures?

Thanks in advance!

[Akser]

Hi @LeoGS,

I think you are looking for Explicit Column Selection (ECS) - which is a Setting in your app. I'm sharing a couple articles that mentions the advantages / risks, hope that puts you on the right track:

Considerations for optimized performance in Power Apps | Microsoft Power Apps

Power Apps Guide - Settings - What's the purpose of the "Explicit Column Selection" Setting? - Power...

[LeoGS]

Thanks a lot for the reply Akser.

This setting is already turned to ON in my app. The reason the column is still fetched is because they are used on the next screens.

My problem is, FOR THE SAKE OF RENDERING THE GALLERY, the columns are definitely not needed and are terribly slowing down everything.

It looks like, the only way I have is to separate the columns to another database?