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.)

Might need an edit. It's not clear whether its26 rows or 26,000 rows as suggested in the code. — TechZen
– TechZen, Commented Dec 10, 2009 at 16:08

TechZen · Accepted Answer · 2009-12-10 16:18:05Z

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.

Community · Accepted Answer · 2017-05-23 11:48:36Z

1

sqlite becomes much faster when you use transactions and parameterized queries: Improve INSERT-per-second performance of SQLite?

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Dec 10, 2009 at 19:27

tuinstoel

7,30629 silver badges28 bronze badges

2 Comments

Carl Coryell-Martin Over a year ago

I think I have the transaction side covered. I could try parameterizing the insert and doing 26k of them...

tuinstoel Over a year ago

Just like compiling code, parsing sql statements take a lot of time. Sqlite will parse every insert statement when you don't use parameterized queries.

jessecurry · Accepted Answer · 2009-12-10 19:46:22Z

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.

I'm trying to add the table to a existing database in the writeable space on the iphone.

Collectives™ on Stack Overflow

Howto: Insert rows into Sqlite quickly on the iphone

3 Answers 3

Comments

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Linked

Related