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

@alex-w Thanks for your explanation.
I encounter the same issue on my dashboard. Do you know if self-hosting would solve this issue? Do you have an estimation of how much the performance would improve then?
Thanks again for your help!

1 Like

Hey @Hugo!

Happy to help here! Hosting on-premise will definitely help query times since you are hosting Retools backend and its in theory closer to your data. This improvement would scale depending on how far you are from our cloud server though (the further the bigger benefit you'd likely see). Our server is currently in the pacific northwest I believe.

Hey everyone! I wanted to callout that we're working on improving performance in Retool and also have this doc on building performant apps :blush:

https://docs.retool.com/docs/app-performance-best-practices

At the time I started this topic almost every SQL and API query (including the simple ones) in the cloud version took 2-5 seconds to complete. Now I use the on-premise version the same queries take 50-300 milliseconds to complete.

@Nick On-premise means self-hoisted?

Yes, self hosted.

@Nick If I self-hoist manually, do I have to again create a table in our new database.?
or
What should be the procedure to test the time execution of our existing data in db.

Hey @ishi8 great question! If you decide to host Retool on-prem and create a new resource, you should still have access to all data stored in those resources. As for your resource setup (the credentials used to connect to your resource) and your specific apps, you would need to set these back up in the new instance. You can export your apps by clicking the 3 dots in the top right corner of the app > Export to JSON. Then, you can import this JSON file in your new self-hosted instance by clicking Create new (home page) > From JSON!

But using this, when I am manually self-hoisting, then using JSON download I am not able to work on tables that already exist, as it says table doesn't exist.

I see :thinking: do you have a resource setup in the new self-hosted instance with the same resource name?