Skip to main content

Query performance best practices

Learn about the best practices for writing performant queries.

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.

In addition, consider the following alternatives which can help reduce query run frequency.

Configure query run delay​

You can add a delay to queries so that a minimum amount of time must pass before a query runs automatically. Configure the Delay between runs option in the Advanced tab. Whenever the query is triggered, it runs only when the specified amount of time has passed. For example, a delay of 5000 would result in a query waiting 5 seconds before running again.

If you display data using a Table component, the built-in filters and search options can provide you with similar functionality. These features function client-side, reducing the need to make repeated requests to a database (e.g., filter to show only active customers or search for a product name).

Query JSON with SQL​

You can use the Query JSON with SQL resource to query JSON data using SQL statements. This allows you to query existing data from other queries, such as retrieving a subset of data.

Transform results​

Transformers allow you to perform real-time transformation of data using JavaScript. You write JavaScript statements that transform or filter referenced data based on your criteria.

You can either create a separate transformer that references other query data or directly transform the results of a query using the Transform results settings.

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.