What is batch, and why can't I use transaction instead?

[cervonwong]

I'm trying to insert or delete a large number of records in a table. E.g. Initializing lookup tables with values when the database is first created, and deleting a large amount of data at once if user selects to delete all.

Right now, I am trying to delete all the rows in a method. The method receives a list of data classes, I see that I can do it two ways:

    batch(
      (batch) {
        for (RECORD in LIST_OF_RECORDS) {
          batch.delete(
            TABLE_NAME,
            RECORD
          );
        }
      },
    );

    // OR

    transaction(
        () async {
          for (RECORD in LIST_OF_RECORDS) {
            await (delete(TABLE_NAME)..where(FITLER)).go();
          }
        }
    );

I can't find much information about what batches are in SQLITE so I assume this is a moor-specific feature. I can't find a section on batches in the official moor documentation either, so this left me abit confused about:

What is the difference between a batch and transaction when I want to efficiently insert or remove a large number of records?
Also, what are scenarios that I should use a batch instead of a transaction and vice versa?

Thanks a lot! :)

[simolus3]

You're correct, batches are a moor-specific feature. There are some docs on batch in the Dart documentation.

There are two major performance considerations when running a large number of updates. First, we want to avoid a write to disk for each statement. Transactions help here, since they effectively only store data when they're committed. Batches also use a transaction internally, so they also solve that problem.
Another issue is that we want to avoid preparing statements multiple times. Let's say that LIST_OF_RECORDS.length == 1000, and assume that each statement has the sql DELETE FROM table WHERE id = ?. With transactions, we'd compile that exact statement 1000 times and run each compiled statement once. With batches, we compile it once and run that compiled statement 1000 times. Compiling statements is fast and caching prepared statements is rarely necessary in sqlite, but in this extreme case batches can be more efficient than transaction. If you're using moor_flutter, moor batches use sqflite's batch mechanism too, which reduces plugin messages.

[cervonwong]

I see that there is no deleteAll method for Batch in moor.

Would putting a for-loop with batch.delete in a batch() be more preferable than a for-loop with delete..where in a transaction()?
From what I understand from your reply, batch compiles the statement once instead of one for each record. And transactions does not do so. Do batches still compile statements once if I'm not using insertAll and replaceAll?

[simolus3]

Do batches still compile statements once if I'm not using insertAll and replaceAll?

Yes! Basically, a batch queues up a Map<String, List<List>> from SQL statements to variables (not quite that, we preserve the correct order). It runs all the statements afterwards. So it doesn't make any difference if you're using insertAll or a loop. In fact,

https://github.com/simolus3/moor/blob/670193984357900823f8dbf6801d092d8e775977/moor/lib/src/runtime/api/batch.dart#L61-L67

Similarly, calling batch.delete in a loop is likely faster than an async for loop in a transaction.

[cervonwong]

Great 😍 I will be using batches for large amounts of initialisation and deleting multiple rows at once. I will be using transactions for a complex create / select statement for weird relationships across tables to ensure they run atomically (and fast).