0

I have a table created with this command:

CREATE TABLE "A" (`id` INTEGER PRIMARY KEY NOT NULL, `b_id` INTEGER NOT NULL REFERENCES B(id) ON DELETE CASCADE, `c_id` INTEGER NOT NULL REFERENCES C(id) ON DELETE CASCADE, `field1` INTEGER, `field2` TEXT NOT NULL, `field3` INTEGER NOT NULL, `field4` INTEGER NOT NULL, `field5` INTEGER NOT NULL)

When I enable foreign key support (via the PRAGMA command), I have performance issues on this table when doing a "large" number of updates (around 3000). I have no problem on other tables (which also contains foreign keys). For this table, the updates are done in ~90seconds whereas it only takes ~1sec for other tables. If I don't enable foreign keys, the ~3k updates are quickly (a few seconds) done.

Few additional notes:
- I use FMDatabase as wrapper of SQLite.
- I wrap my update statements in a transaction.
- The execution time of the transaction seems to be linear with the number of updates in the transaction. For example, if I do only 100 updates, it will take approximately 3 seconds.
- I tried to VACUUM the database, and I even tried to re-create it from scratch. It didn't change anything.

If anyone has an idea about the origin of this issue, please tell!

TreeTop
  • 1

1 Answers1

0

When doing updates, the database needs to look up and check child records. Therefore, the documentation recommends that

an index should be created on the child key columns of each foreign key constraint.

Create an index on A.b_id.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for your answer. I applied the following script to the database before doing the updates: CREATE INDEX AIndexB ON A(b_id); But it had no effect. I also tried to create two indexes (one for b_id and one for c_id since I have 2 foreign keys), but neither did any change. – TreeTop Sep 29 '14 at 15:56
  • There might be other foreign keys in the database. – CL. Sep 29 '14 at 15:59