Insert multiple rows at once

[tp]

Is there any way to insert many (a variable number in my case) rows at once with something like execute?

I have only found these hard-coded values in the test cases, but not any facilities to do this with a list of parameters:

sqlite3.dart/sqlite3/test/database_test.dart

Line 326 in d2c2dcf

..execute('INSERT INTO test VALUES '

So far I am looping over the rows to insert, but I wonder how much fast it could be it they are inserted all in one query :)

I have seen this comment, but in my testing for the initial load if still takes >10ms to insert 3000 rows (across 2 tables), which is just a bit too much IMHO. But I would like to get this sped up so as to not be a blocker, as in all subsequent reads/writes in that project the synchronous nature of this library is much preferred.

[simolus3]

Using prepared statements multiple times is the fastest way to do bulk inserts that SQLite has to offer. Are you wrapping the insert loop in a transaction? If not, each individual call starts a short-lived transaction that needs to be written to disk before we can continue. Putting them in a transaction usually speeds things up significantly.

If things are still too slow with a transaction, using a temporary isolate for that (you can't share database connections across isolates directly, but opening the same database files on different isolates is safe) is the only alternative that comes to mind.

[tp]

Are you wrapping the insert loop in a transaction?

Yes I am doing all the writes via prepared statements in a transactions.

I was hoping that this could be further sped up, as I assumed there must be significant overhead due to the continual context switches between Dart & SQLite (3000 INSERT operations, instead of just one). But maybe I am overestimating that…

I'll keep it in mind that in the worst case I could offer an async batch insert on another isolate to keep the UI thread unblocked. If the practical data does not extend a lot beyond the current amount, the current approach still seems fine though (to have that potential ~1 frame drop on low-end devices).

Thanks for the detailed response.

Just to be sure, technically this could be something that the library can support and would likely speed up my use-case, it's just not something you're currently interested in, right? (Understandably given that there is the "isolate alternative", which will solve this blocking for good, while my suggestion might only improve this for up to maybe 5x and would then block too long as well.)

[simolus3]

continual context switches between Dart & SQLite (3000 INSERT operations, instead of just one).

The overhead of dart:ffi isn't that bad and SQLite runs in the same process, so there's no context switch.

Just to be sure, technically this could be something that the library can support and would likely speed up my use-case, it's just not something you're currently interested in, right?

I'm not sure what "this" is here - SQLite does not offer a function to run multiple statements with parameters in a single call. The current approach you're using for batch inserts is likely already the fastest possible.
(there's certainly potential to profile and optimize this to make this a little bit faster, but it's there's no magic way to make this substantially faster)

[tp]

I'm not sure what "this" is here - SQLite does not offer a function to run multiple statements with parameters in a single call.

Ah, interesting, I just assumed that it would also be workable with parameters just as in the “hard-coded values” case linked above. But if that’s not even possible with the underlying library, then that’s of the table anyway.

Thanks again for your constructive feedback. Feeling good now about the approach I have and the potential extension of offering a batch insert in a separate isolate.