Is there any good way to make multiple select queries in one request and get t\he data loaded into multiple data arrays?
Use case: You have a lot of lookup tables (states, order_status, shipping_type and so on) or you are getting a list of invoices plus the first row's line-items. It would be a lot more performative to use one database connection rather than a half dozen or more.
Edit: Oh, and I should clarify I am using Azure SQL server. I mainly dev in the .Net stack and there are ways to return multiple result sets in a single command. Not sure if it is possible with the tools you have, but my googling does confirm it is a bit of an unusual request.
A Retool capable compromise may be to do just one request to Retool and it does the multiple connections to the server and then returns the results back in one shot. That would certainly help the performance.
Hi Bradly,
Unfortunately you won't be able to do multiple select queries in a single request. The only way to get the data from each of those queries is to perform a join or union across such tables in order to get all the necessary records in a single table. Are these lookup tables across different data sources, or all within the same source?
Same source.
You did re-spark an idea with UNION which I had originally abandoned.
You can return multiple tables of data in one dataset, and thus one connection, if you can make the structures the same and then use UNIONs. Then I could make some transformers or JSON SQL queries that extract the specific records I need for each dropdown (or whatever) and link to those transformers rather than the query.
This is a little less than ideal for my second use case, but workable for the first. More effort of course so one would need to make sure the extra effort is worth the performance improvement.
Does that sound workable on the Retool side of the fence?
1 Like