Bulk insert optimize

[andrefmsilva]

Hi,
I need to get maximum performance when i do some bulk insert.

How can i do something like this and get a prepared statement to execute? I need to use GRDB methods or sqlite-data has support for this?
(another optimisation is to disable foreign keys checks before the insert and enable it after)

INSERT INTO table (
    field1, field2...
) VALUES (
    ?, ?,...
)
ON CONFLICT(id) DO UPDATE SET
    field1 = excluded.field1,
    field2 = excluded.field2
...

[andrefmsilva]

i think i found the documentation, but i can't find how to get a prepared statement for better performance.
https://swiftpackageindex.com/pointfreeco/swift-structured-queries/main/documentation/structuredqueriescore/insertstatements#Conflict-resolution-and-upserts

[andrefmsilva]

With the insert logic like i posted before i get around 2500 inserts per second
But if i use GRDB directly i get around 45k inserts per second.

(results without pragma changes)

let batchSize = 10000
for batch in newItems.chunked(into: batchSize) {
    try await database.write { db in
        
        let statement = try db.cachedStatement(sql: """
        INSERT INTO myModel (
            id, 
            ...
        ) VALUES (
            ?, ...
        )
        ON CONFLICT(id) DO UPDATE SET
            ...
        """)
        
        for item in batch {
            try statement.execute(arguments: [
                item.id, ...
            ])
        }
    }
}

I got the suggestion from here: groue/GRDB.swift#1624

[mbrandonw]

Can you share a full example? There's a lot of "..." in your code that I would want to know exactly what you are doing.

[andrefmsilva]

i'm just seting values.
that model has around 16 fields that i need to insert/update and basically i just list all of them.
In the conflict section i just set all of them (not the primary key ofc), i'm not doing any case or extra logic

[mbrandonw]

Still, it would be helpful to share a full example so that I can run a benchmark. I rather not assume how you are doing things and would prefer just to see the actual query.

[andrefmsilva]

I will create a test file with both insert methods so we can compare results.