Hello,

Assuming I have the following table:

CREATE TABLE SnapshotParameters( snapshot_id integer, object_id integer, param_id integer, param_value integer );

This table contains about 24K records per one snapshot.
Assuming I want to copy values from one snapshot to the other and using the following command:

UPDATE SnapshotParameters AS dst SET param_value=src.param_value FROM SnapshotParameters AS src WHERE dst.object_id==src.object_id AND dst.param_id==src.param_id AND dst.snapshot_id==3 AND src.snapshot_id==5;

This command works fine but the problem is that it takes ~3.5 seconds to complete.

Is there a way to optimize this UPDATE command to run faster?

Many thanks,

PazO

I have no idea, but would just point out that the table lacks the chainer_id column that appears in your query.

Can you try with a preceeding WITH clause? It can also be forced to materialize. Maybe this can help.

param_id doesn't seem to be showing up anywhere in your update query. Is that right, or should there also be a bit saying the param_id's are equal? If you've got more than one param_id per object_id then you're potentially messing things up I believe.

Yes, you are correct, I forgot to add this:

AND dst.param_id==src.param_id

You'd expect faster results with an index on snapshot_id or object_id (preferably whichever of those has fewer duplicates).

One could imagine the current plan is roughly

• Look at every record until you find snapshot_id==3 -- Look at every record until you find matching object_id and snapshot_id==5. Update dst if/when you find the match.

That outer loop executes 24k times, and the inner loop might execute 24k times for each call of the outer loop, for a total of 24k*24k = half a billion times.

If there are very few duplicate object_id's, an index on object_id substantially reduces the work for the inner loop.

If there are very few duplicate snapshot_id, an index there substantially reduces the work for both loops.

Create a unique index on the combination of columns in the join (snapshot_id, object_id) in that order so that the processing can find all the rows which have snapshot == 5 and use the (3, object_id) to find the row to update. Without that index one or the other (or both) devolve into a table scans.

Thank you for the reply.

In my table I have the following indexes:

• Primary Key: (snapshot_id, object_id, param_id)
• Normal: (snapshot_id, object_id)
• Normal: (snapshot_id)

Please note that (snapshot_id, object_id) cannot be unique as a single object in a snapshot has ~200 parameters.

Another solution I considered is value-based:
I found out that about 80% of our parameters are holding the value zero (the data is settings of audio knobs and controls, and the value zero is very popular). I considered the solution of not storing zero in the DB. It adds some logic mechanism but it is solve-able. This can significantly speed up operations done on [groups of] snapshots: merge, copy, paste, etc.

I don't think you need the second two indexes, as the first index can do everything that the others can. Maybe they make lookups a bit faster as they are smaller, but need to be updated for every new insertion slowing that down.

I agree. And we can go even further: make the table WITHOUT ROWID, so the engine will use a single Btree

CREATE TABLE SnapshotParameters( snapshot_id integer, object_id integer, param_id integer, param_value integer, PRIMARY KEY(snapshot_id, object_id, param_id) ) WITHOUT ROWID

No external indexes needed

I agree. And we can go even further: make the table WITHOUT ROWID, so the engine will use a single Btree

 CREATE TABLE SnapshotParameters(
     snapshot_id   integer,
     object_id     integer,
     param_id      integer,
     param_value   integer,
     PRIMARY KEY(snapshot_id, object_id, param_id)
 ) WITHOUT ROWID

By the way, the Delete button (to delete my post) is not working on this forum

After some period of benign participation in this forum, non-anonymous posters are taken out of the subject-to-moderation category. Then, deletion (which is a form of editing) is allowed. Patience should suffice.