Howto: Insert rows into Sqlite quickly on the iphone

Question

Whats going to be the fastest way for me to insert 26,000 rows into a new sqlite3 table on the iphone?

My naive implementation was to create a file foo.sql looking like this:

BEGIN TRANSACTION;
CREATE TABLE user_db.places (
place_id integer primary key, -- cornell id less the leading L
lat        real not null,
lng        real not null,
name       text not null,
country_code text not null,
admin1     text not null,
private    integer not null);

INSERT INTO "places" VALUES(1,50.650002,-114.566666,'Windy Point',0,'CA','AB',0);
....
26k rows more
....
COMMIT;

and then running this code:

sql = [[[NSString alloc] initWithContentsOfFile:candidatePath  
                                       encoding:NSUTF8StringEncoding 
                                          error:NULL] autorelease];
if (sqlite3_exec(database,[sql UTF8String],NULL,NULL,&errorMsg) != SQLITE_OK) {
NSAssert1(0, @"Error loading update file: %s", errorMsg);
}

worked very poorly (35 seconds) probably because it was reading 2.8 MB of file into an NSString, then converting that string into a UTF8 string, and then doing it all at once.

(Hmm, this suggests a couple of improvements right off the bat... I think I'll go ahead and ask this and update it as I learn more.)

Answer 1

I think you'd be better off using a NSFileHandle to read the date file lines one or a few at a time and then insert into the table as you go. Certainly, your memory use will be much more efficient.

More generally, your data should be separate from your code (especially your SQL code.) Your data should be in some raw delimited format. Read it in a line or so at a time and then call the SQL to insert it.

If this is a one time deal you should just create the SQL database on a desktop and just include it in the iPhone project. Having an iPhone do this kind of processing places to much a burden on the hardware (at least more of a burden than most iPhone users will put up with.)

If your going to be using SQL a lot, you should think about using Core Data. Core Data provides an easy interface between the procedural SQL and Objective-C.

Answer 2

sqlite becomes much faster when you use transactions and parameterized queries: http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite

Answer 3

Would it be possible to just import the rows and then store a sqlite database on the iPhone, if the data will always be the same there's no reason to do this step on the phone, you could avoid the insert all together.