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, Mar 15 '12 at 11:37
Do these records change often? Could you create the db on your computer and include it in the project? — JoshRagem, Mar 15 '12 at 13:56
@JoshRagem is there any faster way to fill this sqlite from the json API other than objc? — Akash Malhotra, Mar 16 '12 at 04: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, Mar 16 '12 at 04:59
@JoshRagem I have to hit the API 30k times each time changing an ID parameter , read the json and then add it to sqlite db. Its not possible manually. — Akash Malhotra, Mar 16 '12 at 05:03
@Akash python could do that for you--http://docs.python.org/library/sqlite3.html http://docs.python.org/library/internet.html It shouldn't be too hard. The point is really that you could do the work on the desktop and not on a mobile device. — JoshRagem, Mar 16 '12 at 05:10
@JoshRagem i have python installed on my mac. Never used it though. Actually the api response time is one second per HIT. So do u think its worth trying something else? — Akash Malhotra, Mar 16 '12 at 05:16
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/8951/discussion-between-joshragem-and-akash-malhotra) — JoshRagem, Mar 16 '12 at 05:20

Dave G · Accepted Answer · 2012-03-15T13:48:55.480

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

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. — Dave G, Mar 15 '12 at 11:52
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? — Akash Malhotra, Mar 15 '12 at 12:08
You're re-preparing the statement in every iteration with sqlite3_prepare_v2 move that outside the loop — Dave G, Mar 15 '12 at 12:14
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. — Dave G, Mar 15 '12 at 12:14
@DaveG i moved the prepare outside the lopp but it works only for the 1st iteration — Akash Malhotra, Mar 15 '12 at 12:25
Try adding sqlite3_clear_bindings on your statement at the bottom of the iteration (last statement before starting the next iteration of the loop) — Dave G, Mar 15 '12 at 13:23
@DaveG i got it to run now as you said but its the same speed, even begin and commit dont speed it up :( I have edited my post with new code. — Akash Malhotra, Mar 15 '12 at 13:34

score 0 · Answer 2 · edited May 23 '17 at 11:50

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.

score 0 · Answer 3 · answered Mar 15 '12 at 12:07

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 '12 at 12:07

CarlJ

9,361
3
31
46

Inserting 34000 records from API to sqlite in iphone

3 Answers3