Best Practices for Client-Side Data Processing of Numerous SQL Queries in Retool

Queries and Resources
1

Hi Retool Community,

I’m working on a project where we're facing a unique challenge related to data processing and performance optimization. We have a series of complex SQL queries performing tasks like counting, proportion calculations, ranking, grouping, and filtering on a dataset. Our goal is to minimize the number of queries to the database while efficiently handling these operations.

Here are examples of the types of operations we are performing in SQL via queries:

Aggregation and Grouping: Grouping data by certain attributes and calculating counts, maximum values, etc.
Ranking and Ordering: Assigning ranks based on certain criteria and ordering the groups.
Advanced Calculations: Calculating percentages, handling subqueries, and union operations.

Given the complexity and the need for efficiency, I’m exploring the best way to handle this in Retool. Specifically, I am looking for insights on:

  1. Client-Side Data Processing:
  • Is it feasible to perform these types of aggregations and calculations client-side in Retool, especially for large datasets?
  • What are the best practices for handling such complex data manipulations in the client's browser without significantly impacting performance?
  1. Minimizing Database Queries:
  • How can we optimize our approach to minimize the load on the database, possibly by reducing the number of queries?
  • Are there strategies within Retool for efficient data fetching and processing that can handle these requirements?
  1. Retool Components and Features for Optimization:
  • Are there specific Retool features, components, or techniques that are particularly useful for this kind of complex data processing?
  • Can Retool's JavaScript environment effectively handle such operations?

I appreciate any advice, insights, or guidance on this matter, thank you for your time and help! :blush:- Kiran

2

Thanks for posting about this @kiran - lots of builders face similar decision points when working on building or perfecting performance-optimized Retool apps. This blog post might be a good read to check out when facing these questions: 10 tips to increase speed and performance in Retool Lots of good tips in there. (Shoutout to @stefancvrkotic @sophie)

I'll let others chime in with any additional advice if they have more ideas!

1 Like
3

I would reverse the approach. Your backend / sql environment will be far better at handling complex queries, execution operations (read/insert/update) and you already have the queries in SQL. Hosting cost for a sql database is a consideration but I wouldn't expect it to spike tremendously.

If you attempt to move it on the retool side, it will work with a lot of queries and transformers but you'll have to eventually commit the result back into the database. The 3 types of operations you mentioned will be painful to move and maintain in Retool.

You should consider taking a step back in all the features you want to offer, or even split it in multiple apps, components, or views.

1 Like