Query loading time performance

Retool is an awesome tool and I want to use it to build multiple internal tools. But the loading time is kind of a UX killer and made me doubt if I should use Retool.

To benchmark Retool I’ve created a simple query in Retool to display in a table. I’ve executed the same query in Sequel Pro connected to the same SQL server. The results:
Sequel Pro: 0.150s
Retool: 2.5s

I understand Retool has to process the data trough some extra layers before displaying the data but the difference in loading time is quite a lot.

Is there a way to optimize the query loading time in Retool?

3 Likes

There’s definitely some systematic reasons for this. All of your credentials are applied initially on any request made locally, so it isn’t going through an intermediary. There’s also not much processing or UI that is updated with that data.

In Sequel Pro:
Local System (Credentials are exposed locally) -> Resource -> Local System

In Retool:
Browser -> Retool Server (Credentials are applied to query) -> Resource -> Retool Server -> Browser

So by definition, the same query will always take longer and be more secure than a query run locally to the database. There are a few things we can keep in mind for optimizing query times:

  • The main Retool DB is currently in Quincy, Washington, though we are hoping to provide better regional support in the midterm future.
  • There is a built in query caching option under the advanced tab, which shares the cached result across multiple users. Instead of the full stack trace, if a query is still within it’s cache time and has exactly the same inputs, the request would go Browser -> Retool Server -> Browser and return the previous result.
  • Though more difficult to set up, many queries could benefit from being saved to the user’s cache in localStorage. If the value is already defined, you could disable and populate components with the localStorage value instead. You could also potentially use that as a placeholder to load immediately, while it is updated at a later point and re-saved if the value is different.
2 Likes