On Sun, 2014-04-20 at 21:18 -0700, David B. Lamkins wrote:
> One thing that's interesting is that cf∆append runs about twice as fast
> inside the transaction wrapper than it does without. I imagine that
> SQLite must be running several implicit transactions; these get deferred
> by running the sequence of operations inside an explicit transaction.
>
> Despite that observation, I'm loathe to build-in transaction support for
> the sake of speed. It'd help the case where component files are
> implemented on SQLite, but might not be the right thing to do for other
> databases. Again, this is a policy decision that belongs at a higher
> level of abstraction.
>
Good news!
To paraphrase the SQLite documentation: "Inserts are fast; transactions
are slow." (And, yes: there is an implicit transaction for every SQL
statement unless you explicitly use a bracketing transaction; this
explains the observed behavior noted above.)
The SQLite manual claims that an average desktop machine should be able
to run 50,000 inserts per second. The manual (in the FAQ section) also
notes typical performance of about a dozen transactions per second. This
jives with what I'm seeing in my test cases.
Here's a snippet of code I've added to my performance test to batch
10,000 inserts into a single transaction:
grouped←{⍺ cf∆append ⍵ data}
0⍴ {⍺ grouped¨⍳⍵} cf∆with_transaction {tn} 10000
This construction, which wraps a single transaction around all of the
other database activity, does cf∆append at a rate of ~10,000 per second!
Since each cf∆append executes two inserts (one for the data and one to
update the current oid), the actual insert rate is ~20,000 per second.
I'm not as happy with the syntax of this construction; I'll need to
ponder whether there's a better approach. (Or maybe it'll seem more
obvious to me in the morning...)
Coincidentally, it doesn't work to replace the `grouped' identifier by
its corresponding lambda in the second expression. Should this be be
possible in GNU APL? I'm seeing a crash when I try it...
