2

I am trying to insert 10000 records and its taking 45 secs

this is my code 

println(NSDate.new())
for index in 0...10000{
countrys.insert(name <- "abc")
//println(index)
}
println(NSDate.new())

is this way to do it?

vinbhai4u
  • 1,329
  • 3
  • 19
  • 36

1 Answers1

8

The issue is that SQLite will have to commit each of the INSERT statements individually. You should consider using transactions. You can start a transaction with transaction method (which performs BEGIN TRANSACTION SQL) and then use commit to commit them (which performs COMMIT SQL).

For example:

db.transaction(.Deferred) { txn in
    for i in 0 ... 10000 {
        if countries.insert(name <- "abc").statement.failed {
            return .Rollback
        }
    }

    return .Commit
}
Rob
  • 415,655
  • 72
  • 787
  • 1,044
  • 1
    Can you please help me with some code, I am fairly new to iOS and swift. – vinbhai4u Jun 29 '15 at 05:14
  • Just FYI, I am trying to convert whole JSON to sqlite table, if you know a better way please let me know thanks @Rob – vinbhai4u Jun 29 '15 at 05:16
  • 1
    @vinbhai4u Glancing at this library, you'd either call `transaction` and `commit`, or judging from the API, it would appear you can call the `transaction` method with block parameter. See code snippets in my answer. – Rob Jun 29 '15 at 05:33
  • @vinbhai4u If the JSON is uniform (_i.e._, all objects have the same keys), you could try a bulk insert with a single statement, but you'd have to forgo the type-safe interface (till the library can provide such an interface itself), _e.g._, manually construct a statement and array of values: `"INSERT INTO table (c1, c2, c3) VALUES (?, ?, ?), (?, ?, ?)"`, etc. – stephencelis Jun 29 '15 at 11:39
  • There are tons of ways of doing it. See revised answer for just one example. – Rob Jul 25 '15 at 23:19