Clear Query Cache

[ericonline]

Hello, 

I'm timing the performance of Azure SQL vs. On-prem SQL vs. On-prem DB2 vs. Sharepoint Online queries. Queries are only basic at this time: 

ClearCollect(
    colAzure,                         //same thing for other sources
    dbo.everyrecord
)

Subsequent queries after a collection has been cleared are cached somewhere. I know this because if I clear colAzure, for example, and run the ClearCollect again, the timer does not run. The results "just appear" after a second. 

Questions: 

1. Where is this data cached?

2. How do I clear it? 

Thank you

[PeteMc]

Hi ericonline,

There is a function called refresh to help with this situation: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-refresh

In your case you would use:

Refresh(colAzure)

Note that the app caches data to help speed up the app and to prevent frequent calls to the SQL server to help reduce its load.

[ericonline]

Hm. I get the error: "Only managed connections can be refreshed." when running Refresh(colAzure). Makes it sound like only external data sources as opposed to Collections can be Refreshed. 

Thoughts?

[wyotim]

Hey @ericonline, while this may not be the most time-friendly suggestion, does closing out of the app and restarting it clear the collection? My limit understanding of collections is that they are temporary in nature and when an app is closed they are cleared out. I am not aware of any caching and would be quite interested to learn more about what is happening with that.

Another thought I had is checking the Reset condition of your timer and making sure it is working correctly. I only suggest that because I have on more than one occasion overlooked that field when setting up animations.

Final thought: what about setting up a rotating filter so that any caching wouldn't be able to factor in? Like a date range or FK or something of that nature that changes each time you run the collect? It may not give the most realistic results depending on the size of the data (and could be a bear to set up depending on the structure/content of the table you use) but at least you might be able to see trends given enough trials.

Okay, final final though: what about connecting to the same table twice (TestTable and TestTable_1) and try alternating between collecting one and then the other into the same collection to "fool" PowerApps into thinking it is collecting different data each time? A very theoretical idea, but it might just work.

I'd be interested in hearing what you come up with!

[ericonline]

Thanks @wyotim, those are some good ideas! 

RE: Close out/reopen app: You're right, that one might be a time sinker. Would be ideal to just hit a "clear" button or the like. I'll be connecting/disconnecting from VPN, etc during testing. 

RE: "Rotating filter": That is a great idea, and likely the solution unless someone knows of a way to otherwise clear cache. I wonder if the cache might still play a factor in the times? Hm.

I'll put some more thought into your other ideas too and see how it plays. Will report back!

Take care

[v-xida-msft]

Hi @ericonline,

Could you please share a bit more about your scenario?

Do you want to know where the Collection is stored and how to clear it?

For your first question, the Collection that you created using Collect function or ClearCollect function is cached within your local device (local to the app). In other words, the Collection is cached within the current session of your app.

For your second question, when you close your app, the Collection that you created within your app would be deleted automatically. Currently, there is no direct way to delete the Collection within PowerApps currently.

Best regards,

Kris

[seadude]

Thank you @v-xida-msft. I will mark your answer: "Currently, there is no direct way to delete the Collection within PowerApps..." as the answer.

[acosatti]

Thank you all for your comments/replies on this issue.

The fact that collections are cached is really annoying while developping an app. 

When any ClearCollect statement is perform to feed a collection from a SharePoint list (the collection contains data), the collection is not updated with the most recent content of the list.

Example:

ClearCollect(
// Reload lookup data from SP (Items may have been added)
CollEntities,
Entities
);

does not update the content of the CollEntities collection with the SharePoint list.

It's really a pain to work with this limitation. The Refresh function does not work on collections.

Is there a new version of PowerApps planned to fix this problem?

[wyotim · Accepted solution]

Hey @acosatti, at the risk of oversimplifying or suggesting something you may have already tried, have you tried:

// Reload lookup data from SP (Items may have been added)
Refresh(Entities);
ClearCollect(
    CollEntities,
    Entities
)

Refreshing the data source before collecting it should re-cache the data so that you see any recent changes. Refresh indeed doesn't do anything for collections but for data sources, it should do the trick!