Skip to main content

Query performance best practices

Learn about the best practices for writing performant queries.

Monitor query performance

You can monitor the performance of queries to determine where performance issues may be occurring.

It's important to continually monitor the performance of apps and workflows, and make improvements where needed. Performance issues can happen for any number of reasons and can be difficult to replicate, especially when they involve external factors.

Follow these practices to identify, resolve, and prevent query performance degradation to keep apps and workflows working efficiently as possible.

Allow for additional query overhead

Retool queries add a small amount of overhead to queries (approximately 150ms) when compared to making an API request or querying a database directly. This additional overhead allows Retool to:

  • Download results
  • Transform data using JavaScript
  • Assign values to relevant components
  • Calculate further dependencies

This overhead shouldn't have any noticeable affect and may only be apparent when compared to a direct request.

Separate query logic to reduce round trips

If you have many similar queries (e.g., multiple queries for the same database table), consider using query JSON as SQL or JavaScript transformers to reduce the number of requests sent to your resources. These allow you to query a resource once, and then further manipulate or transform the data as needed.

Limit the number of queries that run automatically

You can configure apps to run queries on page load or whenever there's a relevant input change. While this can help keep your app updated, users cannot fully interact with the app until those queries are complete, making the app feel unresponsive for a short time.

Limit the number and size of queries that run automatically. Use event handlers to trigger queries under certain conditions or use watched inputs to run the query automatically when certain inputs change.

Use query caching to your advantage

You can cache query results for a specified period of time to reduce the number of queries to a resource. You can also cache queries for users across your organization. If you’re running large analytical queries, or don't need immediate data, consider using caching to improve performance.

For example, if you cache a query and 100 users run the query within the cache duration, your resource is accessed only once instead of 100 times. After the first query, the following 99 queries return significantly faster. Depending on your use case, even a 5-10 minute cache can prevent extraneous round trips.

Be careful of stale data, and programmatically invalidate the cache when updates are made.

Retrieve only required data from large data sets

Rendering thousands of rows of data into a table impacts browser performance. Instead of loading large amounts of data, consider transforming or filtering data in the query. Many APIs support pagination, so you can filter responses to only return data you need. The Table component supports using server-side pagination to allow for more specific data requests.

Put complex logic in workflows or backends

Overuse of complex JavaScript can impact the Retool frontend (the part of the app with which users interact). If you need to perform complex logic or querying of data, avoid doing this solely with JavaScript. Put this logic into the Retool backend (the part of the app that interacts with resources), or use Retool Workflows.