cancel
Showing results for 
Search instead for 
Did you mean: 

Storing SQL table data in cache memory to improve performance.

Storing SQL table data in cache memory will improve performance of the app. 

Status: Under Review
Comments
Level 10

This should also be made possible in the case of CDS tables.

Level 10

Hi all.

The most efficient way I have found so far to cache all of a data source into PowerApps is the following:

ClearCollect(CachedInstruments, Sort('[Access].[Instruments]', ID, Ascending));
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1000,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
;
UpdateContext({MaxID: Max(CachedInstruments, ID)});
If(CountRows(CachedInstruments)=1500,
	Collect(CachedInstruments, Filter(Sort('[Access].[Instruments]', ID, Ascending), ID > MaxID))
)))

Please note that the If statements are nested, so that it only advances if more records are present, if not then it stops.  It does not matter how many loops you copy-paste since perfomance will not be affected.

 

Normally I have the landing page just welcome the user and have the above code in a "Proceed" button.  You can show progress to the user with a text box next to the button to show CountRows(CachedDataSource) so at least the user know that something is going on.

 

I have used the above for up to 5,000 records and it was quite fast.  I also have a 20,000 row table, but normally I load the piece I need (using the above code but filtering the data source for the portion I need with input from the user once he selects the group for which he wants analysis)

Level: Powered On

My SQL table has more than 17lakhs data. In that case, I cannot use the above code.

Level 10

Agreed, we really need delegation of CountRows and Sum so that we can avoid having to cache all of such large tables.  The above work-around has worked for up to 10,000 lines (filtered from a much larger set), and it is probably impractical for more.

Administrator
Status changed to: Under Review
 

Microsoft product group have one magical method in their export to csv in entity CDM ng-click="$ctrl.selectFormatAndExecute(format)" , this is in their CDM export UI.

 

I have requested them to expose via powerapps. This method-selectFormatAndExecute exports 300k records to csv within 30 seconds!! That means they must have access to 300k real fast without 500 limit.

Labels