4

I have to make an sqlite db from data fetched from a JSON API. The code is working fine and its adding them one by one via a for loop, but the api response time is 1 sec per hit, so 34000 seconds plus inserting them in sqlite through code will take about 9 hours. Is there any way to speed up this?

Edit: i am using Objective C/sqlite3 framework/Xcode 4.2

Heres the Code...

 dbPath=[self.databasePath UTF8String]; if(sqlite3_open(dbPath,&database)==SQLITE_OK) { // sqlite3_exec(database, "BEGIN", 0, 0, 0); const char *sqlstatement="insert into artist values(?,?,?,?,?)"; sqlite3_stmt *compiledstatement; if(sqlite3_prepare_v2(database,sqlstatement , -1, &compiledstatement, NULL)==SQLITE_OK) { for(i=4611;i<=34803;i++) { NSURLResponse *response; NSError *err; NSData *data= [NSURLConnection sendSynchronousRequest:[NSURLRequest requestWithURL:[NSURL URLWithString:[NSString stringWithFormat:@"API&id=%i",i]]] returningResponse:&response error:&err]; if(data.length>0) { NSError *err; NSDictionary *jsonDict=[NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingAllowFragments error:&err]; // sqlite3_exec(database, "BEGIN", 0, 0, 0); sqlite3_bind_text(compiledstatement,1,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"id"] UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_text(compiledstatement,2,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"name"] UTF8String], -1, SQLITE_TRANSIENT); if([[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"]) sqlite3_bind_text(compiledstatement,3,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"] UTF8String], -1, SQLITE_TRANSIENT); else sqlite3_bind_text(compiledstatement,3,[@"" UTF8String], -1, SQLITE_TRANSIENT); if([[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"]) sqlite3_bind_text(compiledstatement,4,[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"] UTF8String], -1, SQLITE_TRANSIENT); else sqlite3_bind_text(compiledstatement,4,[@"" UTF8String], -1, SQLITE_TRANSIENT); if([[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0]objectForKey:@"url"]) sqlite3_bind_text(compiledstatement,5,[[[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0] objectForKey:@"url"] UTF8String], -1, SQLITE_TRANSIENT); else sqlite3_bind_text(compiledstatement,5,[@"" UTF8String], -1, SQLITE_TRANSIENT); if(sqlite3_step(compiledstatement)==SQLITE_DONE) { NSLog(@"done %i",i); } else NSLog(@"ERROR"); } sqlite3_reset(compiledstatement); } } } else NSLog(@"error"); sqlite3_close(database);
Improve this question
9
  • 2
    9 hrs for 34K records? you are doing something very wrong... Commented Mar 15, 2012 at 11:37
  • @MitchWheat I have posted the code. Please help Commented Mar 15, 2012 at 11:39
  • Do these records change often? Could you create the db on your computer and include it in the project? Commented Mar 15, 2012 at 13:56
  • @JoshRagem is there any faster way to fill this sqlite from the json API other than objc? Commented Mar 16, 2012 at 4:37
  • @Akash I don't know, but if the records you are getting don't change too often I thought you could fill the SQLite db on a desktop and put the file in the project so it is part of the app. You could then update the db gradually, via the app, as records change. A simple 'last updated' date stamp would be enough for you to use for checking for updates. Does that seems reasonable? Commented Mar 16, 2012 at 4:59

3 Answers 3

Reset to default
6

Would it be possible to restructure your code so that you're not opening the database on each iteration?

  • Open Database
  • Begin transaction sqlite3_exec(..., "BEGIN", ...)
  • compile statement
  • iterate data set
    • insert record
  • finalize compiled statement
  • Commit transaction sqlite3_exec(..., {"ROLLBACK" or "COMMIT"}, ...)
  • Close database

This is in contrast to what you have now

  • Iterate dataset
    • open database
    • compile statement
    • insert record
    • finalize compiled statement
    • close database

The overhead of doing it the way your doing would account for the performance. Try refactoring to the method I outlined above and see how you do.

EDIT

I've reformatted your code to indicate where I'm talking about. Additionally, I think the other performance hit your taking (as indicated by another user) is the JSON call. That may be TRULY what is slowing you down so much.

dbPath=[self.databasePath UTF8String]; if(sqlite3_open(dbPath,&database)==SQLITE_OK) { sqlite3_exec(database, "BEGIN", 0, 0, 0); const char *sqlstatement="insert into artist values(?,?,?,?,?)"; sqlite3_stmt *compiledstatement; if(sqlite3_prepare_v2(database,sqlstatement , -1, &compiledstatement, NULL)==SQLITE_OK) { int hasError= 0; for(i=4611; hasError == 0 && i<=34803; i++) { NSURLResponse *response; NSError *err; NSData *data= [NSURLConnection sendSynchronousRequest:[NSURLRequest requestWithURL:[NSURL URLWithString:[NSString stringWithFormat:@"API&id=%i",i]]] returningResponse:&response error:&err]; if(data.length>0) { NSDictionary *jsonDict=[NSJSONSerialization JSONObjectWithData:data options:NSJSONReadingAllowFragments error:&err]; // sqlite3_exec(database, "BEGIN", 0, 0, 0); sqlite3_bind_text(compiledstatement,1,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"id"] UTF8String], -1, SQLITE_TRANSIENT); sqlite3_bind_text(compiledstatement,2,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"name"] UTF8String], -1, SQLITE_TRANSIENT); if([[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"]) sqlite3_bind_text(compiledstatement,3,[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"description"] UTF8String], -1, SQLITE_TRANSIENT); else sqlite3_bind_text(compiledstatement,3,[@"" UTF8String], -1, SQLITE_TRANSIENT); if([[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"]) sqlite3_bind_text(compiledstatement,4,[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"links"]objectForKey:@"website"] UTF8String], -1, SQLITE_TRANSIENT); else sqlite3_bind_text(compiledstatement,4,[@"" UTF8String], -1, SQLITE_TRANSIENT); if([[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0]objectForKey:@"url"]) sqlite3_bind_text(compiledstatement,5,[[[[[[[jsonDict objectForKey:@"artistDetail"]objectForKey:@"artist"]objectForKey:@"media"]objectForKey:@"low_res_images"]objectAtIndex:0] objectForKey:@"url"] UTF8String], -1, SQLITE_TRANSIENT); else sqlite3_bind_text(compiledstatement,5,[@"" UTF8String], -1, SQLITE_TRANSIENT); if(sqlite3_step(compiledstatement)==SQLITE_DONE) { NSLog(@"done %i",i); } else { NSLog(@"ERROR"); hasError= 1; } } sqlite3_reset(compiledstatement); } // Really need to check error conditions with commit/rollback if( hasError == 0 ) { sqlite3_exec(database, "COMMIT", 0, 0, 0); } else { sqlite3_exec(database, "ROLLBACK", 0, 0, 0); } } sqlite3_close(database); } else { NSLog(@"error"); }
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

One other thing is that the database is in auto commit mode by default. You may want to execute a BEGIN statement prior to entering the loop (possibly before compiling the statement) and then executing COMMIT on successful completion or ROLLBACK if there is a failure in the middle somewhere. This way your database doesn't get messed up by a bad run.
i have tried your way by removing insert compile stmt and open stmt outside for loop, still its the speed. I updated my code above. Any ideas?
You're re-preparing the statement in every iteration with sqlite3_prepare_v2 move that outside the loop
Also try the BEGIN/{ROLLBACK,COMMIT} outside of the loop, every time you run sqlite3_step as configured you're opening/committing a transaction on every iteration. By executing a BEGIN before the loop, you open one transaction that is used for the entirety of the iteration, then it is committed or rolled back AFTER the loop ends.
@DaveG i moved the prepare outside the lopp but it works only for the 1st iteration
|
0

You can do the following,

  • Create Query with [NSSting StringWithFormat:@"Insert Statement with Parameters"]

  • Store the queries into an array.

  • Create a transaction . You can do that via SQL Query

  • Loop array and execute query.

  • commit transaction

.

For every insert statement sqlite begins a transaction and commits it. That is heavy. To avoid such a overhead we can start our transaction. It is very very fast.

Improve this answer

Comments

0

The biggest bottle nek is the api call.

The best practice solution is, to separate the calls with a NSOperation within a NSOperationQueue.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.