DELETE statement runs multiple times

Related Projects GRDB
1

Hi,

I execute the following code:

_=try Layer.deleteAll(db, keys: layers.map({$0.id!}))

I know that layers.count is only 1. Still the output in the debugger window shows that for a small database this query is still run multiple times. This seems to be caused by the code in Database+Statements.swift:250.
It seems it consumes rows from SQLite, but why would a simple DELETE statement return multiple rows (for a larger database more rows are returned). The SQLite DELETE documentation doesn't mention anything about returned rows.

Thanks in advance.

Kind regards,

Remco Poelstra

2

Hello @remcopoelstra. This question has already been asked in the Github issues: https://github.com/groue/GRDB.swift/issues/624

Github issues are a wonderful source of experience for GRDB users: it's often worth searching through them before asking a question.

3

You're absolutely right. I searched the forum, but will also check the GitHub issues next time.
It seems that the trace is output for every foreign key constraint that's cascaded.
Leaves me wondering why I can insert 45000 rows in a matter of seconds, but deleting these rows takes about 2 minutes. This is a pure SQLite problem though, as the command line shows the same behaviour.

4

2 minutes is alarmingly slow.

If you do not wrap all those deletions in a single database transaction, consider doing it. It should considerably improve the performance.

And if you still experience odd performances, use Instruments and profile your app!

5

I either delete (in my app) a single row from the parent table (where the foreign key constraint cascades the delete) or perform the equivalent on the command line: DELETE FROM elements WHERE layerId = 1. Both take about the same time.

I added an index on layerId but that didn't help at all.

I did a profile in instruments and it says that all this time is spend in sqlite3_step(). The .timer on feature of the command line confirms this (shows about the same time spend).

6

OK. And the index should be a good idea. But do you make sure those deletions are wrapped in a single transaction, as suggested?

7

I believe it is, as the 'full' code is:

try database.write { db in
    _=try Layer.deleteAll(db, keys: layers.map({$0.id!}))
}

In my test case, the layers.count is 1.

8

Maybe you're not familiar with GRBD transaction handling. Compare:

// N transactions
try dbQueue.write { db in
    try Layer.delete...
}
try dbQueue.write { db in
    try Layer.delete...
}
...
try dbQueue.write { db in
    try Layer.delete...
}

// One transaction (faster)
try dbQueue.write { db in
    try Layer.delete...
    try Layer.delete...
    ...
    try Layer.delete...
}