Tuning PosgreSQL/Wappler query performance

[Notum]

Hello Wapplers,

• I have project built on NodeJS 16 + Docker + PostgreSQL 12.4
• I have table with 10000 records (dummy data for now)
• I’m running a server side workflow which goes trough each records and updates them
  
  
• It takes ~105 second in average to run this job
• During this job I’m monitoring PosgteSQL dashboard and there is maximum 200 transactions per second in average
  
  

My task is to lower the execution time for this workfow.
What I’ve tried far:

• Customizing PostgreSQL config file with
  I found on-line tool config generation tool https://www.pgconfig.org/ and applied it with Docker command. After I checked the config from PostgreSQL itself SHOW all; I ran task again and got same 105 seconds and maximum 200 transactions per second in average
• Running this task under Wappler parallel
  
  
  
  I’m getting ~125 seconds and 160 transactions per second in average
  What’s Wappler is actually paralleling?

So my question to all Wappler enthusiasts - what would you else suggest to decrease execution time of this workflow?

Thank you reading.

[Apple]

@Notum

What’s Wappler is actually paralleling?

Nothing, absolutely nothing.

Vote for the feature request:

Parallel Repeat

The current Parallel block of the NodeJS server target doesn’t allow to parallelize an array, only a series of hard-coded steps - meaning you cannot do a database query and parallelize actions over each element/row/item. An example makes it easier to understand. Let’s say you have a table “websites” with URLs, such as: https://wappler.io https://microsoft.com https://bing.com https://google.com https://youtube.com If you want to perform an API Action (fetch those websites) in parallel, you ca…

Also check my other feature request for additional information concerning database performance:

Smarter Database Multi Insert

Continuing the discussion from Slow insert records into the database: Actually, I’ve just realized how slow is the Database Multi Insert in Wappler, even with transactions as mentioned in my previous post. I did the same test using a third-party SQL viewer, with the import feature, and it basically transforms the CSV into a bulk INSERT statement: INSERT (...) VALUES (...), (...), (...), (...), (... about 300) INSERT (...) VALUES (...), (...), (...), (...), (... about 300) // instead of Wapple…

[Notum]

Already voted!

[Apple]

I believe the easiest way to speed up execution available at this time is to use something like NocoDB and its update API

Meaning you build a new array of “members” (your variable name) with your modified data and do an API Action to your NocoDB to bulk update:

REST APIs

Has the downside of needing to add a third-party software to the stack, so you’re no longer solely using Wappler - more loose parts, more chance of breaking stuff

Here, one more feature request:

Database Insert/Update/Delete from JSON

Please consider a database query “users”: [ { "id": 1, "name": "George", "country": "NL" }, { "id": 2, "name": "Teodor", "country": "BG" } ] That’s a JSON representation of our rows. Through a series of steps, I modify the names: [ { "id": 1, "name": "George P.", "country": "NL" }, { "id": 2, "name": "Teodor T.", "country": "BG" } ] Now, I want to UPDATE the databas…

You can start betting which one is going to be picked first

[George]

You can try to use a single database update action that inserts/updates all the records at once.

We added support for those with the new Form Repeater, see:

[George]

You can try to use a single Database insert/Update action for all your updates.
We have added support for it with the new form repeater, see:

Using App Connect Form Repeater with Update Record Forms

Intro Form repeater can be used in update record forms in order to easily update nested data to your database table. Similar to our Insert Data tutorial - we will show you how to update a user and its contacts - so make sure to check it first, before continuing here. The user will be stored in the main table, while its contacts will be stored in a sub table. Overview We created an update record page with a list of records an a form: [Screenshot_93] Our dynamic list gets its data from a …

[JonL]

It would be useful to update the devdocs to add expected input format for the database insert/update actions for single record and multiple records.

As it’s abstracted users may think that it only works with the AC form repeater as in the tutorial example.

[Notum]

Thanks for topic link, but I think my case is a little bit different.

1. This will be backend workflow without any interaction with Web UI
2. I’m creating randomized array from SQL query output, then I’m going trough this array and using {{index}} in update action and I doubt this can be replace with any other workaround.

[scalaris]

A Postgres stored function is the easiest and most performant approach

[Notum]

I respect your short answer yet a bit details would be more valuable.

[scalaris]

I suggest reading up on this area. A google search will unearth hundreds of documents.

E.g. https://www.tutorialspoint.com/postgresql/postgresql_functions.htm

You can call stored procedures directly in Wappler using the customer query action. I use this approach regularly when trying to maximise DB performance

[JonL]

1. I agree with @scalaris that a stored procedure is quite performant although it has caveats. But for this case @Notum I would certainly consider it.

2. @George have you guys considered adding an sql editor to test and run queries in the DB manager. Specially to create functions and triggers. I think they have their space in Wappler. Users could create functions and then you guys could create an SC action wrapper function to call these and return the results. Similar to what supabase does.

Database Functions | Supabase

[scalaris]

Just my thoughts on building this additional feature. The Wappler team’s ‘can do’ attitude is great and they appear to love a challenge - BUT, these decisions should always be tempered by the wider needs of the Wappler platform.

It’s so simple to call a stored procedure or function via the custom query action that any experienced Wappler user isn’t going to want to have the relative unknown of a Wappler wrapper in the middle - offering little or no additional functionality and potentially causing issues.

It’s much the same as the Stripe library of actions - they offer nothing that the API action cannot do. It’s simply like-for-like wrappers that introduce their own issues - To work best with Stripe you need to understand their APIs - I’ve built a significant Stripe integration with API actions having been frustrated with Wappler’s intermediary issues. It was much quicker and less stressful to do so. Now I expect some will question this but I’ve been there and done it - how many of the Wappler team eat their own dog food.

What would be far more useful would be documented and working examples of using Stripe with the API action. This, however, as we know isn’t a strong point for the team. But it’s actually what Wappler’s paying customers need as is evidenced on the support forums. Sometimes it’s not about the code - it’s the documentation. the same for custom queries with stored procedures and functions.

Just to clarify - I LOVE WAPPLER. I use it daily and have built a large system with it. But it’s the business decisions they make that frustrate me - they need to focus more on the actual needs of the customer and on solving them.

Great companies never want to push their ideas onto customers, they simply give them what they actually need.

[JonL]

@scalaris

stored procedure

Wappler users read that and shit their pants. (Except @Antony who is one with the procedure). That’s why Wappler and its abstraction exists

That’s the single purpose of no(low)-code tools actually.

Not saying documentation isn’t critical. It is.

[scalaris]

@scalaris

experienced Wappler user

experienced Wappler user - as outlined

[Notum]

experienced Wappler user != experience DB administrator
It might be relatively easy to execute stored DB function from Wappler but it might be difficult to write such function in SQL.

[scalaris]

@Notum

what would you else suggest to decrease execution time of this workflow?

You asked for suggestions to decrease execution time of this workflow. You didn’t specify experience as a requirement. but looking at the server action above, it’s fairly trivial and there’s loads of documentation available.

Good luck with whatever you choose!

[Antony]

@JonL, my pants are as clean as a whistle.

In all seriousness though, I appreciate you pointing out that they are a management nightmare… it did curtail my initial enthusiasm for them to a suitable level.

As for tuning performance… while it doesn’t answer the original question:

The thing that has made my app go at the speed of light is creating loads of database views. They take a good knowledge of SQL, but for a large app, have literally saved the day.

I guess all you 20-year-full-stack-experience-developers know that already, but for us newbie-app-entrepreneurs, it is the little secret that Wappler doesn’t address and makes your app run at warp speed!