Savepoint vs. transaction for performance?

[hyperknot]

I'd like to optimize performance for batch insert operations, for example when a database is restored from JSON dump (say 1000 INSERT statements are run).

I'm using OPFSCoopSyncVFS + sync build in a worker.

I have the following questions:

1. With wa-sqlite (without using experimental WAL modes, etc.) is it better for performance to wrap the INSERT statements with BEGIN/COMMIT or SAVEPOINT/RELEASE?
2. Is SAVEPOINT also helping with performance, or only BEGIN?
3. What happens when I do the following?
   BEGIN
   SAVEPOINT
   ... 1k INSERT
   RELEASE
   COMMIT

Is there anything else I'm missing for optimizing batch inserts?

Python has a special command executemany(), but I haven't seen an analogue of this in either node's better-sqlite3 nor in wa-sqlite.

I guess, outside of a special C-level batch insert function, our best bet is wrapping it in a transaction or savepoint?

[rhashimoto]

FYI, I have never had a need for a nested transaction so I have only played briefly with savepoints.

Answers:

1. The SAVEPOINT docs say, "When a SAVEPOINT is the outer-most savepoint and it is not within a BEGIN then the behavior is the same as BEGIN DEFERRED TRANSACTION", so performance should be essentially the same.
2. What really matters for performance is not creating a separate transaction for each insert.
3. I think that the SAVEPOINT/RELEASE is unnecessary since there is already a transaction. SQLite could optimize it away but I don't know whether it does or not.

Is there anything else I'm missing for optimizing batch inserts?

I coincidentally posted a drive-by response about this last week.