search

appsmithorg / appsmith

Platform to build admin panels, internal tools, and dashboards. Integrates with 25+ databases and any API.
https://www.appsmith.com
Apache License 2.0
34.35k stars 3.72k forks source link

[Feature] Cache results of actions that auto-execute on page load to reduce flickering #3530

Open sharat87 opened 3 years ago

sharat87 commented 3 years ago

Since the auto-load queries execute every time we switch pages, there can be a lot of flickering when we have a lot of tables with queries. If we can cache these results at least in the browser at a session level, that can reduce such potential flickering at least until the browser is refreshed. This stems from the below broader message posted on Discord:

I've created a page that makes loads of queries, some when the page is loaded and others when a customer is selected. Some of the results of these queries are just look up data that are not expected to change frequently so it would be useful to be able to set a "time to live" (TTL) for these queries and cached results are returned within the TTL.

sharat87 commented 3 years ago

Conversation on this (pasting almost verbatim here for giving the background and context):

I was more concerned about reducing unnecessary load on the database but obviously there is a performance aspect as well. Possibly there is not a load issue as I haven't done any measuring but it just seems like good sense to not hit the database unless needed especially if you know the data has not changed

Yeah I agree. This is something to carefully think about with regards to how this is going to look and feel for the overall UX of action execution. Following along that train of though, say there's a configurable-TTL based caching for actions, I'm guessing there should also be a way to force fetch from the database bypassing the cache as well, right?

I was leaning away from session cache and just cache based on SQL statements so the same query with different query parameters would be cached individually - for general lookup queries that typically have the same query parameters the result would be cached once and shared with all users

Right, but they would still need a predictable way of busting the cache, TTL or force-load or both (useful when the data in the database changes externally). For general queries not dependent on user information, I'm guessing the query SQL itself would be the same for all users and so this same caching mechanism would work out in our favor.

sumitsum commented 3 years ago

Different but somwhat related: https://github.com/appsmithorg/appsmith/issues/8639