SQLite: mass updating a field without cursor

Question

I have the following table:

CREATE TABLE Records (
RecordIndex INTEGER NOT NULL,
...
Some other fields
...
Status1 INTEGER NOT NULL,
Status2 INTEGER NOT NULL,
UpdateDate DATETIME NOT NULL,
CONSTRAINT PK_Records PRIMARY KEY (RecordIndex ASC))

And an Index:

CREATE INDEX IDX_Records_Status ON ClientRecords
  (Status1 ASC, Status2 ASC, RecordIndex ASC)

I need to fetch the records of a certain status one by one, so i used this statement:

SELECT *
FROM RECORDS
WHERE RecordIndex > @PreviousIndex
AND Status1 = @Status1
AND Status2 = @Status2
LIMIT 1

But now I need to fetch the records sorted by another field, but this field is not unique for each record, so I can not use it in the same way. So I decided to add a new SortIndex field to my table.

As there are no cursors in SQLite, I am doing the following to initialize the values for SortIndex.
First I create a temporary table:

CREATE TEMP TABLE Sort (
SortIdx INTEGER PRIMARY KEY AUTOINCREMENT,
RecordIdx INTEGER )

Then I fill this table in the correct sort order:

INSERT INTO Sort
  SELECT NULL, RecordIndex
  FROM Records
  ORDER BY SomeField ASC, RecordIndex ASC

Then I create an index on the temporary table:

CREATE INDEX IDX_Sort_RecordIdx ON Sort (RecordIdx ASC)

Then I update the SortIndex field in my Records table:

UPDATE Records
SET SortIndex =
  (SELECT SortIdx
   FROM Sort
   WHERE RecordIdx = RecordIndex)

Then I drop the temporary table:

DROP TABLE Sort

And finaly I create a new index on my Records table

CREATE INDEX IDX_Records_Sort ON Records
  (Status1 ASC, Status2 ASC, SortIndex ASC)

This allows me to do the following select

SELECT *
FROM Records
WHERE SortIndex > @PreviousSortIndex
AND Status1 = @Status1
AND Status2 = @Status2
LIMIT 1

The problem is, as the table contains around 500K records the whole thing takes around 2 minutes. It would probably have been a lot faster to initialize SortIndex with a cursor, but SQLite lacks this feature :(

Is there a faster way to do this ?

Thanks in advance !

Answer 1

Instead of doing an UPDATE with a correlated subquery, you should consider the INSERT OR REPLACE feature of SQLite, which will perform an UPDATE of a whole row when the primary key is a duplicate:

UPDATE Records
   SET SortIndex =
       (SELECT SortIdx
          FROM Sort
         WHERE RecordIdx = RecordIndex) 

becomes

INSERT OR REPLACE INTO Records (RecordIndex, SortIndex, ...)
SELECT RecordIndex, SortIdx, ... FROM another_temporary_table_containing_all_columns.

Instead of using a temporary table containing all columns you can of course use a SELECT which joins the old table and the new one: try this inside the SQLite shell

CREATE TABLE original (id INTEGER PRIMARY KEY, content TEXT);

BEGIN TRANSACTION;
INSERT INTO original(id, content) VALUES(1, 'foo');
INSERT INTO original(id, content) VALUES(2, 'bar');
INSERT INTO original(id, content) VALUES(3, 'baz');
COMMIT TRANSACTION;

CREATE TABLE id_remap(old_id INTEGER, new_id INTEGER);

BEGIN TRANSACTION;
INSERT INTO id_remap(old_id, new_id) VALUES(2,3);
INSERT INTO id_remap(old_id, new_id) VALUES(3,2);
COMMIT TRANSACTION;

INSERT OR REPLACE INTO original (id, content)
SELECT b.new_id, a.content
  FROM original a
 INNER JOIN id_remap b
    ON b.old_id = a.id;

SELECT * FROM original;

Result:

1|foo
2|baz
3|bar

Another option if you need to do mass updates but do not want a correlated subquery is to perform the join in a view, and to create a trigger INSTEAD OF UPDATE on that view. A problem is that you cannot have constraints that fail during the process. I suppose that the constraints are checked for each row so that might be very slow.

In the SQLite shell:

CREATE TABLE original (id INTEGER PRIMARY KEY, content TEXT);

BEGIN TRANSACTION;
INSERT INTO original(id, content) VALUES(1, 'foo');
INSERT INTO original(id, content) VALUES(2, 'bar');
INSERT INTO original(id, content) VALUES(3, 'baz');
COMMIT TRANSACTION;

CREATE TABLE id_remap(old_id INTEGER, new_id INTEGER);

BEGIN TRANSACTION;
INSERT INTO id_remap(old_id, new_id) VALUES(3,6);
COMMIT TRANSACTION;

CREATE TEMPORARY VIEW tmp_id_mapping
    AS
SELECT a.content, b.old_id, b.new_id
  FROM original a
 INNER JOIN id_remap b
    ON b.old_id = a.id;

 CREATE TEMPORARY TRIGGER IF NOT EXISTS tmp_trig_id_remap
INSTEAD OF UPDATE OF content ON tmp_id_mapping
    FOR EACH ROW
  BEGIN
    UPDATE original
       SET id = new.new_id
     WHERE id = new.old_id;
   END;

UPDATE tmp_id_mapping
   SET content = 'hello';

SELECT * FROM original;

Result:

1|foo
2|bar
6|baz