bulk inserts with SQLite and CoreData

Question

I have a CoreData model that uses SQLite as persistence store. I need to insert large numbers of rows after doing some processing to each record. Is there any way to send those commands to SQLite

PRAGMA synchronous=OFF PRAGMA count_changes=OFF PRAGMA journal_mode=MEMORY PRAGMA temp_store=MEMORY

I need to speed up the processing time, as it takes couple of hours to complete.

Any hints will be appreciated.

Thanks

Christian Kienle · Accepted Answer · 2012-01-25 11:12:07Z

You can specify the pragmas when adding your store to the store coordinator:

NSMutableDictionary *pragmaOptions = [NSMutableDictionary dictionary]; [pragmaOptions setObject:@"OFF" forKey:@"synchronous"]; [pragmaOptions setObject:@"OFF" forKey:@"count_changes"]; [pragmaOptions setObject:@"MEMORY" forKey:@"journal_mode"]; [pragmaOptions setObject:@"MEMORY" forKey:@"temp_store"]; NSDictionary *storeOptions = [NSDictionary dictionaryWithObject:pragmaOptions forKey:NSSQLitePragmasOption]; NSPersistentStore *store; NSError *error = nil; store = [psc addPersistentStoreWithType:NSSQLiteStoreType configuration: nil URL:url options:storeOptions error:&error];

(Adapted from Persistent Store Features)

I strongly suggest to also read "Efficiently Importing Data".

Related documentation: NSSQLitePragmasOption Efficiently Importing Data

You should be aware of the consequences the memory mode has: If something bad happens (crash, exception, force quit, ...) you will lose the data which is in the in memory journal. If this is what you want: Go for it.
Yes. journal_mode = OFF. But if I were you I would not use this option either. There are a lot of other things you can do to improve performance when importing data. Playing around with the pragmas is only a last resort. Read the documentation I linked.

Collectives™ on Stack Overflow

bulk inserts with SQLite and CoreData

1 Answer 1

3 Comments

Hot Network Questions