Bulk insert with SQLite.swift, performant way?

Question

I'm trying to do a bulk insert with SQLite Swift. However, I'm not 100% sure if my code is correct.

The Swift code I've settled on (because it delivered the best performance timewise) is:

do {
   try DB.transaction { () -> Void in
     for index in 0...num_docs {
       table.insert(data <- "test", data_num <- index)
      } 
    } 
} catch _ {
 throw DataAccessError.Transaction_Error
}

EDIT ----

If I would use the following code in swift, inserting 10000 docs drops from +/- 12 secs to 0.8 secs. What sounds too good to be true.

  let docsTrans = DB.prepare("INSERT INTO TEST_DB (data, data_num) VALUES (?,?)")
    do {
       try DB.transaction(.Deferred) { () -> Void in
         for index in 0...num_docs {
           try docsTrans.run("test", index)
          } 
        }
     } catch _ {
            throw DataAccessError.Insert_Error
        }

It confirms my own performance tests: https://github.com/groue/GRDB.swift/wiki/Performance — Gwendal Roué, Jan 12 '16 at 12:37
Looking at the code, this (unfortunately) makes a lot of sense. The table.insert function will generate a 10000 sql statements which are then processed by SQLite one at a time. By contrast, doing the `prepare` first is one call, and `run` calls `sqlite3_step` which just passes through the data to be inserted. Short of providing a bulk insert method, or trying to do statement prep & caching in SQLite Swift, it's hard to imagine how to do this faster with the `table.insert` design. Nice job coming up with the alternative! — mm2001, Jan 14 '16 at 01:14
@Swennemen thank you for the question and the solution! Can you please share the definition of the DB varibale? — Ahmet Akkök, Sep 24 '16 at 10:58
Thanks for this find swennemen! It took 45 seconds for me to insert 6000 items into my db and this raw form only takes 1.8 seconds. That's 26 times faster! — Bret Smith, Jan 26 '17 at 20:46

Bulk insert with SQLite.swift, performant way?

0 Answers0