Wait data to start javascript transformer

[Geool]

Thank you very much @Kabirdas for your help.
This solution works very well indeed.

Maybe you can help me with another problem related to this.

My queries take a long, long time to run... from 10 to an infinite second at times... and it's quite random when the page is refreshed.

I call 3 different APIs and 1 DB ....

Is there a way to optimize this?

Is it a good practice to put the data in the cache?

[Kabirdas]

Query caching is a great idea! There are some docs on it here. Since you're experiencing inconsistent query runtimes in particular there could be a number of things going on though. We have some general docs on debugging query performance, but it would be good to know the resource type of the queries and specifically how each one is behaving.

I've seen, for instance, JavaScript queries hang when they return a promise that doesn't resolve properly or one of the queries they trigger fails and isn't handled properly. SQL queries, on the other hand, can timeout if a connection limit has been reached to the DB. That's really just a couple of examples off the top of my head though, it'd be great to get more information!

[Geool]

I confess that I do not understand why the requests take so much time...

I've been trying to optimize all this for 2 days but nothing works...

the requests take between 10 and 400seconds to execute despite the caching of the data...

if you can look at the code eventually I'm interested because I'm not making any progress...

[Kabirdas]

from the screenshot you posted it looks like there are a few REST queries along with a few Postgres queries. Is there any difference in how the two are behaving? It might also help if you could share some example screenshots of the query runtimes from a couple of the longer running requests:

[Geool]

Hello,

Here are the screenshots as requested:

Can you explain why this request takes 47 sec to execute while on Postman for example it takes less than 1sec?

The time is random but never takes less than 15/20sec which is a lot for a user.

What is the frontend (post processing)?

Same thing for these two requests too, long to execute while postman or elsewhere it's 2/3 sec maximum ...

thanks for your help.

[Kabirdas]

It looks like there are at least two separate issues here:

1. Post-processing is the time it takes for your frontend to update the model and re-render components once the data has been returned. What components are you rendering the data in? If it's a table, what column types are you using and what data mapping do you have in the component itself? Are there multiple components that are dependent on this data, maybe down the line (e.g. is there a table that renders this data, and then another component that renders based on a property of the table, etc.)?
2. The backend execute resource and data transfer times respectively have to do with the amount of time it takes the Retool backend to query your API and receive data, and how long it takes to transfer that data to your frontend. Since you're hitting an EU endpoint this might cause additional latency but I'm not sure that would account for the full 15 seconds from the screenshot. Would you mind if I stepped into your app and tried running the requests to see how the request times differ when run from a machine closer to the Retool host server?

I'm also curious to know where you're seeing the most variance in the query run times. You mentioned it always takes 15/20 sec to run, is that time distributed across backend and post-processing? Or does one of them seem to be consistently slow?

[Geool]

Yes you can look at the application.

The data are not loaded in visible.
They are in the inputs to have perfect matches, then once the search is launched they appear in a table and finally once the line is selected, the data appears in a modal...

And even with cached data the query takes time to run

But I do not understand why the frontend time is specific to mdlw_get_allcontracts when it is not this component that is called in the application but only _fullData_mdlw_tre_doc_prest ...

You can look to help me to clarify these problems.

Thanks

[Kabirdas]

Thanks for letting me take a look at the app! I'm seeing similar execute resource times with a significantly lower transfer data time which might indicate some of the issue is distance latency:

If you run themdlw_get_allContracts using a tool like pgsql are you also seeing significantly shorter execution times?

The post-processing time that's happening does seem to be a result of the JS code you're running. You can test it with something like this after the await Promise.allSettled call in your code:

const start_t = new Date(); 
/*
your code here
*/
console.log('time to run:', new Date() - start_t);

When trying that on my end the time reported there seemed to match with the post-processing time for the query. Can you let me know if you see the same on your end?

It might be useful to introduce some kind of pagination but I'm not sure if that would fit your use case, is there a particular reason that you're fetching all of the data at once?

[Geool]

Thank you for all your answers.

Here is the result I get with one of the longest queries I have: odoo.

Concerning sql, I run the query on dBeaver and here is the result which is instantaneous.

I also did two explain to see the difference between Retool and dBeaver: here are the two results.

Concerning the data retrieval, I retrieve all the data to put them in a single table with a merge between them or not. And I retrieve this one to inject them in my inputs in order to have perfect searches/correspondences... Why I did not paginate ? Simply because I don't know how to do it and I never did it...

Maybe this can solve the problem?

[Kabirdas]

I think paginating would make the one query go faster but since it looks like there might be a significant amount of latency because of the distance to the server making more requests to your DB might not be ideal. You might consider using something like localStorage to store your data. How often does the data update? Is it possible to request just the updates?

Also, how much of your data does the user need to act on? There may be other filters that are beneficial as well.

As mentioned here EU hosting is a project that's being worked on! Hopefully, that helps your query performance as well when it arrives.

[Geool]

Okay I understand better the latency then if it communicates with the American servers.

For the LocalStorage, it is possible to imagine everything in reality as long as the development time is not too important. But again I don't know how to do it.

Can you help me with this. The base is called once massively and the evolutions are minimal in reality because they will be mainly additions in the base which exists at the rate of perhaps 50/100 lines per day.

Thanks again for your help.

[Kabirdas]

It looks as though there's a significant amount of logic in the script you have that's processing the query results so I'm hesitant to make a super strong recommendation here. You might want to explore looking at the developer network to see if you can find someone who can work more closely on your app with you.

For localStorage, you can set it using the setValue method described here. Once you've done so, it should persist between apps. You can then access the values you've set using localStorage.values.yourKeyName.

That being said, it's possible that refactoring your code could make it easier to paginate your query and also just increase performance generally and might be a better way to go than storing things in localStorage.

[Geool]

thank you for your feedback.
It is not possible to delegate the work of this application.

for the LocalStorage, what should I put in it?
my js files ?
my call to the Postrges DB ?
my API calls to trello, docusign, prestashop, odoo ?

I'm a bit lost ^^

[Kabirdas]

It's actually a built-in variable that you can reference using event handlers and scripts:

It's one of the ways Retool enables sharing data between apps (docs here).

After setting it you can access the same variable in other apps:

Note: I'm using a large dataset in the screenshots above which seems decently performant but I'm not doing a lot of other things with it. I don't know how much this will help and you'll still need to process the data at some point which I expect will take time.

[simonriskjaer]

Hi @Geool

I am trying to query Odoo - can you share how you've setup the "odoo_master_post_res_partner" query in retool? Is it using Odoo's xml-rpc API, or are you interfacing with the underlying postgres database?

Thank you!

[Geool]

Hello @simonriskjaer,

For odoo, I initialized it as an api with login / password.

Then I made a classic request in jsonrpc.

POST : URL
Headers : Content-Type -> application/json
Body : Raw
{
"jsonrpc": "2.0",
"method": "call",
"params": {
"service": "object",
"method": "execute",
"args": [
"YOUR_BDD",
429,
"YOUR_PASSWORD",
"YOUR_TABLE",
"search_read",
[],
[ YOUR_FILTERS : "name", "id", "city" ... ]
]
},
"id": YOUR_ID_CONNECTION
}

AND IN TRANSFORMERS YOUR_ALGORITHM

[simonriskjaer]

Hi @Geool ,

Thank you for helping out! I'm new to retool and jsonrpc.

I've tried setting it up using the REST resource:

• It seems the query returns the contents of our landing page instead of the API response. Can you see what I've setup incorrectly?
• In the raw body, the last param ""id": YOUR_ID_CONNECTION" - what is this?

[Geool]

I don't know about your code, but here's how I did it:

first step:
Create in resources - > Rest API -> Basic Auth -> Id -> Password

second step:
In the project, create a REST API file

For the connection to obtain the connection id
you can search the Internet for explanations on how to do the process with Postman and obtain the id

[simonriskjaer]

Hi @Geool,

Thank you for the detailed explanation - I've now managed to get data from Odoo.

Thanks again!