cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Clear Query Cache

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Clear Query Cache

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!

View solution in original post

8 REPLIES 8
PeteMc
Level 8

Re: Clear Query Cache

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.

Super User
Super User

Re: Clear Query Cache

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?

Super User
Super User

Re: Clear Query Cache

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!

Super User
Super User

Re: Clear Query Cache

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

Community Support Team
Community Support Team

Re: Clear Query Cache

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

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.
Super User
Super User

Re: Clear Query Cache

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
Level: Power Up

Re: Clear Query Cache

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?

 

Super User
Super User

Re: Clear Query Cache

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!

View solution in original post

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (7,980)