Inserting 34000 records from API to sqlite in iphone

Question

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

9 hrs for 34K records? you are doing something very wrong... — Mitch Wheat
– Mitch Wheat, Commented Mar 15, 2012 at 11:37
Do these records change often? Could you create the db on your computer and include it in the project? — JoshRagem
– JoshRagem, Commented Mar 15, 2012 at 13:56
@JoshRagem is there any faster way to fill this sqlite from the json API other than objc? — Akash Malhotra
– Akash Malhotra, 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? — JoshRagem
– JoshRagem, Commented Mar 16, 2012 at 4:59

Dave G · Accepted Answer · 2012-03-15 13:48:55Z

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");
}

edited Mar 15, 2012 at 13:48

answered Mar 15, 2012 at 11:44

Dave G

9,82238 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Dave G Over a year ago

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.

Akash Malhotra Over a year ago

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?

Dave G Over a year ago

You're re-preparing the statement in every iteration with sqlite3_prepare_v2 move that outside the loop

Dave G Over a year ago

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.

Akash Malhotra Over a year ago

@DaveG i moved the prepare outside the lopp but it works only for the 1st iteration

|

Community · Accepted Answer · 2017-05-23 11:50:45Z

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.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Mar 15, 2012 at 12:07

Vignesh

10.2k3 gold badges39 silver badges73 bronze badges

Comments

CarlJ · Accepted Answer · 2012-03-15 12:07:24Z

0

The biggest bottle nek is the api call.

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

answered Mar 15, 2012 at 12:07

CarlJ

9,5213 gold badges36 silver badges47 bronze badges

Collectives™ on Stack Overflow

Inserting 34000 records from API to sqlite in iphone

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related