Takes about 60 - 70 seconds to insert around 10000 data. Do I need to commit transactions?

Question

I am trying to insert about 10000 data into my database. Check below function for code. It takes about a minute to insert all data. Now I already added block in transaction but do I need to Commit it or it manages automatically. How can I write it in below code?

func insertDataToDB(objects: [DataModel]) {
        removeAllData()
        createSchemaForData()
        print("Start Inserting Connectors \(Date())")
        let stmt = try? db.prepare("INSERT INTO product (connectorId, deviceId, current, status, cost, voltage, power, type, method, mode) VALUES (?,?,?,?,?,?,?,?,?,?)")
        do {
            try db.transaction {
                for product in objects {
                    try stmt?.run(product.connectorId!, product.deviceId!, product.current, product.status, product.cost, product.voltage, product.power, product.type!, product.method!, product.mode)
                }
            }
        } catch {
            print("Failed Inserting connectors: \(error.localizedDescription)")
        }
        print("End Inserting Connectors \(Date())")
    }

removeAllData() Will remove all data from db before inserting.

createSchemaForData() Will create schema if required.

struct product {
    static let table = Table("product")
    static let connectorId = Expression<String>("connectorId")
    static let deviceId = Expression<String>("deviceId")
    static let current = Expression<Double?>("current")
    static let status = Expression<String?>("status")
    static let cost = Expression<Double?>("cost")
    static let voltage = Expression<Double?>("voltage")
    static let power = Expression<Double?>("power")
    static let type = Expression<String?>("type")
    static let method = Expression<String?>("method")
    static let mode = Expression<String?>("mode")
}

you can use `begin transaction` before data insertion start and after data insertion complete use `commit transaction`. **Reference Link** : https://stackoverflow.com/questions/14631477/how-to-insert-40000-records-fast-into-an-sqlite-database-in-an-ipad — Kuldeep, Apr 23 '18 at 05:17
Possible duplicate of [How to insert 40000 records fast into an sqlite database in an iPad](https://stackoverflow.com/questions/14631477/how-to-insert-40000-records-fast-into-an-sqlite-database-in-an-ipad) — TheTiger, Apr 23 '18 at 05:28
I agree with all of you, that that this smells like the traditional "you need to use transactions" problem, but it looks like he is using `db.transaction`, which presumably is doing transactions for him. I think we need to peel this onion a bit further to see why it's not doing the transaction like we think it should, if indeed that is the issue. There might be something else going on here... — Rob, Apr 23 '18 at 05:30
@Rob you have understood my question correctly. I am already applying transaction while Inserting data. So there is something else which is creating issue. — Parth Adroja, Apr 23 '18 at 05:38
I'm not sure if it does automatically handle `BEGIN` and `COMMIT` — TheTiger, Apr 23 '18 at 05:41
Are all of these fields simple text/numeric fields? Or are one or more large BLOB fields? I ask, because large objects in SQLite can bring it to its knees. FYI, I confirmed that `db.transaction` does the necessary `BEGIN TRANSACTION`/`COMMIT` stuff (inserting 100,000 records took 0.5 seconds with `db.transaction` and 123 seconds without), so I don't think the routine "use transactions" observation is relevant here. There is something else going on. But I cannot reproduce the problem on the basis of the code in your question, so you have to give us [MCVE](http://stackoverflow.com/help/mcve). — Rob, Apr 23 '18 at 06:05
Okay @Rob, I will let you know once I create a sample project and try to execute similar flow. — Parth Adroja, Apr 23 '18 at 06:12
@Rob I am not using BLOB fields. All are simple have a look to struct product on the updated question. — Parth Adroja, Apr 23 '18 at 06:16
OK. I admittedly used SQLite.swift as a thin wrapper around the SQLite API in my test: https://gist.github.com/robertmryan/0e4927548b9c5cae6dd36b9697af3294. But before I try again using your example, I'd love it if you could confirm you can reproduce the problem in a nice, tidy MCVE, before I test further. Thanks! — Rob, Apr 23 '18 at 06:19
@Rob Thanks! I have retracted my close vote as duplicate of ... as `BEGIN` and `COMMIT` are being handled here. — TheTiger, Apr 23 '18 at 06:36
@ParthAdroja Glad you solved it. But it is related to something that hidden part what we even can not think without seeing the code. — TheTiger, Apr 23 '18 at 09:50
@TheTiger Sorry for that as even I wasn't aware about that until I did a full debug of code. — Parth Adroja, Apr 23 '18 at 09:54

score 0 · Answer 1 · answered Apr 23 '18 at 09:11

There is no issue with the code above but what I was doing wrong was with the singleton class I created.

I was using the connection like below, which gave me new connection every-time I access the db object.

var db: Connection {
    let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
    return try! Connection("\(dbPath)/\(dbName)")
}

So improved the code from looking code of Rob's in gist. So now the time is not even a second to insert data.

let db: Connection = {
        let path = NSSearchPathForDirectoriesInDomains(
            .documentDirectory, .userDomainMask, true
            ).first!
        return try! Connection("\(path)/EVDataBase.sqlite")
    }()

Let me know if there can be still improvements to the code above. Or if anyone can share their singleton or manager class that would be helpful too.

**Declaration:** `static let shared = Connection("\(path)/EVDataBase.sqlite")`. **Usage:** `Connection.shared.transaction` — TheTiger, Apr 23 '18 at 09:53
Yep, this computed property would definitely cause the problem you describe. Replacing it with a stored property fixes that. Good catch! — Rob, Apr 23 '18 at 15:34

Takes about 60 - 70 seconds to insert around 10000 data. Do I need to commit transactions?

1 Answers1