16sec to insert 1000 rows in a SQLite DB on the iPhone?

Question

I tried to insert 1000 rows in my sqlite db, but it took more than 16 sec.. Is there something wrong in my code that I didn't see ?

 NSLog(@"--start--");

 if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
  static sqlite3_stmt *compiledStatement;

  for(int k = 0; k < 1000; k++)
   sqlite3_exec(database, [[NSString stringWithFormat:@"insert into myTable (id, name) values ('%i', 'a')", k] UTF8String], NULL, NULL, NULL);

  sqlite3_finalize(compiledStatement);

  sqlite3_close(database);
 }

 NSLog(@"--stop--");


start : 2010-11-27 11:21:11.704 
stop  : 2010-11-27 11:21:27.908

thx !

Answer 1

This is due to SQLite's auto-committing. You need to call sqlite3_execwith:

BEGIN TRANSACTION

Now insert all your records as before. After you are done with that. Execute:

COMMIT TRANSACTION

That should do the trick.

Also this FAQ question will explain why it is so much slower. It's actually much faster than a few dozen queries per second as stated in the FAQ, but due to the nature of flash memory on the iPhone, that would seem to normal.

Answer 2

I finally added those 2 lines around my insert queries :

sqlite3_exec(database, "BEGIN", 0, 0, 0);

sqlite3_exec(database, "COMMIT", 0, 0, 0);


start : 2010-11-27 13:07:26.022
stop  : 2010-11-27 13:07:26.285

thanks for your help !

Answer 3

you probably need to do a bulk insert instead of doing 1000 trips to sql

Answer 4

are you sure you do the inserts in one transaction? SQLite is extreamly slow if you auto commit on each insert.