Smarter Database Multi Insert

Feature Request
,
#1

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 Wappler:
INSERT (...) VALUES (...)
INSERT (...) VALUES (...)
INSERT (...) VALUES (...)

The difference is huge, like, HUGE! From about 40 seconds to 5 on Postgres (~11000 rows)

We need a smarter Database Multi Insert! And btw, also allow adding ON UPDATE clause so I don’t have to use a custom query:

INSERT (...) VALUES (...), (...), (... about 300) ON UPDATE SET present_at=:P2
Database Insert/Update/Delete from JSON
Tuning PosgreSQL/Wappler query performance
Query result to server API
How Fast Can You Build Apps? And Career Advice for a Newbie Developer
[NodeJS] Database Bulk Insert
#2

As great as this would be, the way this works is you can have anything inside the repeat, not just insert step.
What makes more sense here is to have a new multi-insert component that does what you are looking for.

It would actually be something that can be created with custom module even now, but it would be a better UX if available natively in Wappler.

#3

A custom module definitely would solve this.

There are any plans to a Wappler marketplace to make custom modules widely available, for free or charging for it?

#4

I don’t think the Wappler user base is big enough for this yet.
There are less than a hundred users I think who dwell in custom modules and formatter extensibility.

We have realased a few free extensions under #wappler-extensions and 1 paid.

2 Likes
#5

Thats not a Wappler issue but a single insert database issue.

#6

Will see if we can add bulk insert action. It is already available in Knex for NodeJS that we use.

Seems that when wrapped in transaction, database inserts are executed much faster.

Transactions start and end actions would be also very nice to have, so we will consider those as well.

@patrick

7 Likes
#7
#8

It is actually a Wappler issue with the default or only Database Multi Insert offered.
As to whether it is worth it to add something to Wappler justified by some indeterminate “user base” – this should not be kicked down the road based on whatever user base numbers.

The CSV script written for multiple records insert definitely should have the incorporated Single File Insert option just as even PHPMYADMIN offers this option. It is the ONLY thing that will get a job done on some servers where an insert done one at a time might time out according to the client’s hosting environment.

#9

@ NewMedia I was rethinking this … and you are right.

1 Like
#10

Postgresql can read directly from a json array (from a wappler query or from post body) and insert/update multiple rows from a single query - incredibly efficient that looping through an array to facilitate multiple row insert.

#11

That’s a really nice tip! Unfortunately, it looks like Wappler has a problem binding stuff to this custom query:

INSERT INTO mytable SELECT * FROM json_populate_recordset(null::mytable, json :P1);

:P1 is replaced as “$” during query testing, no idea why (even when binding text instead of JSON)

#12

I have got it to work multiple times - can you pls share the exact err you get?

$ replacement is an indicative thing that err msg gives - it is not a literal mapper replacement.

#13

Screenshot1
Screenshot11406×782 66.3 KB

If I run the query without binding parameters, it works:

INSERT INTO mytable SELECT * FROM json_populate_recordset(null::mytable, json '[{ "name": "Hello" }]');

Oh, good to know! I think in MariaDB it actually replaced the binding parameters instead of $ in error messages. Currently using PostgreSQL

#14

I wonder if you need quotes around your json?

INSERT INTO mytable SELECT * FROM json_populate_recordset(null::mytable, json CONCAT("'", :P1, "'"));
#15

Didn’t work :frowning:

Syntax error at or near “CONCAT”

I think when you bind a string it actually puts quotes around. I wonder if this is a binding bug

#16

an example of how we use simple json array to bulk insert with a single query without loop

insert into app.my_table (f_1, f_2, f_3)
select :P1 as f_1, B::int as f_2, :P2 as f_3 from json_array_elements_text(:P3) as B;

:P1 is int like 12
:P2 is int like 34
:P3 is simple array like this: ["20","64","25"]

this is for postgresql. not sure how this would work on maria/mysql or if it is possible even!

it could also work for an array of objects - the query will have to be modified accordingly. diff functions will have to be used.

#17

Friendly bump.

I’m seeking ways for optimization of my app, and it seems like this feature could help a lot.

#18

So am I understanding correctly that the current MULTI INSERT is just a regular insert inside a repeat?

#19
#20

Bulk insert is now available in Wappler:

1 Like