Prepared statements and MutablePersistableRecord's

[foxware00]

I'm in a profiling exercise and we have a couple of locations where the database is doing a lot of repeated inserts. The example is - inserting 30 conversations and for each conversation 30 messages. So worst case 900 insert statements.

We wrap everything in a transaction, but I'm exploring prepared statements and wondered if you could shed some light for me

We insert in the following way

public func insert(_ db: Database, _ items: [Entity]) throws {
    for var item in items {
        try item.insert(db)
    }
}

So the question is, would we benefit from pulling out the INSERT statements into raw sql and using prepared statements, possibly caching them. Or is this what's happening under the hood when we call MutablePersistableRecord.insert(_ db: Database)

If this isn't happening under the hood, would there be a friendly way to get the INSERT statement from MutablePersistableRecord so we could cache it ahead of calling .insert.

I believe I'm reading that you're using a cache for all statements generated via GRDB so the optimisations are already present.

Thanks in advance

[groue]

Hello @foxware00,

Inserts use a cached statement, but there are still a lot of computations that are repeated on each iteration of the loop.

If benchmarking your app (in Release configuration) reveals that you spend too much time inserting records, please see #926 (comment) for a longer discussion and a technique for achieving the best performance.