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 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