What is batch, and why can't I use transaction instead? #987
-
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? Thanks a lot! :) |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 3 replies
-
You're correct, batches are a moor-specific feature. There are some docs on 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. |
Beta Was this translation helpful? Give feedback.
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 sqlDELETE FROM table WHERE id = ?
. With transactions, we'd compile that exact statement 1000 times and run each com…