Best practices to build object of objects out of SQL queries

Hi there and happy holidays!

I’m building a new app and my biggest concern here is how to make it run as fast as possible.

I have a few SQL queries, each of them queries one table and each table is linked to the others. So when I query table A, I’ll get ids of rows from table B.

My goal here is to create objects out those queries and store each object into a parent object in a state.

Each of my queries runs below the second and I’m satisfied with this performance. Now, I’d like to run a JavaScript script to build my object of objects without slowing down my running time.

At the end, I’ll use this object to render values into an HTML component.

Those of you who have been there before, would you :

  • run all your SQL queries in the first place and then trigger your JavaScript code to create the object
  • run the first SQL query, triggers on success a JavaScript script to create the top-level properties of all my nested objects then resume SQL queries and trigger on success JavaScript after every successes

I wonder if declaring global variables will help processing faster or not. My concern here is the JS part as it runs client-side and might deteriorate my overall performance.

Also, I know for sure I’ll have to deal with LIMIT and OFFSET for one of my SQL query. Never been in this territory before, what’s the best usage here? I’ll probably limit results to 100 rows so I can keep querying fast.

Thanks for your tips!

Hey @jeremy!

Does the JS query you're running impact the SQL queries at all? If not, it may be best to have them run and then build the object all at once in order to minimize the number of times your app model needs to be updated. You may even want to explore using a Query JSON with SQL query to build your objects.

Also, in case you haven't seen it already, here's a doc on general best practices for performance that may have some things you'll find interesting!

As far as using LIMIT and OFFSET, we have some options for setting things up with tables that might be useful. It may help to know more about how your users will be interacting with the data in your app!