Batching individual updates

[frankvollebregt]

Hello! I noticed my code running slowly and narrowed it down to me doing many small updates, the majority of which can probably be put into a batch. The problem is that I'm struggling to figure out a 'good' way to do it.

Essentially I have two tables, one referencing the other. Let's say there's Apples and Trees, where each Apple contains a reference to its Tree's TreeId. Now Tree (for some backend sync reasons) also has a TreeUuid.

CREATE TABLE trees (
  treeId INT NULL,  -- filled in from backend
  treeUuid TEXT NOT NULL PRIMARY KEY -- not in backend, filled in, used as key in app
);

CREATE TABLE apples (
  ...,
  treeId INT NULL,  -- filled in from backend
  treeUuid TEXT NULL REFERENCES trees(treeUuid) -- not in backend, needs to be filled in by update
);

What the update should do, is update all Apples, by finding the corresponding Tree and adding a reference to the TreeUuid to the Apple.

My current approach looks a bit like the following:

List<Tree> trees = await this.allTrees().get();

// for each tree
for (Tree tree in trees) {
  // update all apples for this particular tree
  update(apples)
    ..where((apple) => apple.treeId.equals(tree.treeId))
    ..write(ApplesCompanion(treeUuid: Value(tree.treeUuid)));
}

How can I go about doing this in a batch?

Should I use a custom query to join the apples and trees and use that to update? Since there's quite a few combinations of tables I need to process, that'd require quite a bit of manual query writing, which isn't ideal (but if it's the best solution then so be it).

Thanks in advance for the help :)

[North101]

This should work:

await batch((batch) {
      for (final tree in trees) {
        batch.update(
          apples,
          ApplesCompanion(treeUuid: Value(tree.treeUuid)),
          where: apples.treeId.equals(tree.treeId),
        );
      }
});

This would probably be even faster, though its SQL only (I don't think moor support UPDATE FROM):

UPDATE apples
SET
  tree_uuid = tree.tree_uuid
FROM trees
WHERE trees.tree_id = apples.tree_id

However, I think the better solution is to just remove apples.tree_id and instead get the tree_id from the trees table.

[davidmartos96]

Also, have you tried using a transaction like Simon suggested?

await this.transaction(() async {
    await Future.wait(....)
});

[frankvollebregt]

Hi David. Thanks for your suggestion!

Essentially, throughout my data model (which I get from the server) the tables are linked using regular (auto-inc) ids, and in the app I want to use uuids instead, such that I can add entries without causing any conflicts. Right after inserting my data, I then execute these 23 queries to set the uuids where appropriate.

Unfortunately, using a transaction like you suggested (either with or without the Future.wait statement) does not affect the running time of the code for me.

[frankvollebregt]

And in actuality, apples and pears are just an abstraction for orders, order lines, activities, products and all sorts of tables used in my actual database schema, to make the question a bit clearer without any context or knowledge of the actual system

[frankvollebregt]

Well now I feel stupid.

After struggling with this issue for longer than I'd like to admit, I started commenting out part of the operations, ending up only updating apples (so 1 out of the 23 operations), followed by not updating any of them. The operation then still took 1.5 seconds. That can't be right, I said to myself.

It turns out I timed not only these update operations, but also the statement where I enabled foreign keys, which takes up all the time.

The operation (wrapped in a transaction, thanks @davidmartos96 ;)) takes about 0.004 seconds now...

I'm not sure why or if enabling foreign keys should take so long, but it's definitely a separate issue from this thread, so if I don't figure it out I'll start a new discussion thread.

[davidmartos96]

@frankvollebregt Nice find :) You could start the investigation by taking a look at the output of EXPLAIN QUERY PLAN UPDATE ..... Any SCAN TABLE you see without an index is essentially looking at each row one by one