Insert 34000 records from API to sqlite in iphone
I need to render sqlite db from data received from JSON API. The code works fine and adds them one by one via the for loop, but the api response time is 1 second per hit, so 34000 seconds plus inserting them into sqlite through the code will take about 9 hours. Is there a way to speed this up?
Edit: I am using Objective C / sqlite3 framework / Xcode 4.2
Here is 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);
+3
source to share
3 answers
Is it possible to change the structure of the code so that you don't open the database on every iteration?
- Open database
- Start transaction sqlite3_exec (..., "BEGIN", ...)
- compilation instruction
- repeat dataset
- insert record
- completion of a compiled statement
- Commit transaction sqlite3_exec (..., {"ROLLBACK" or "COMMIT"}, ...)
- Close database
This is unlike what you have now
- Iterate dataset
- open database
- compilation instruction
- insert record
- completion of a compiled statement
- close database
The overhead associated with doing this is how your actions will take performance into account. Try refactoring with the above method and see how you do it.
EDIT
I have reformatted your code to indicate where I am talking about. Also, I think the other performance affected your take (as another user points out) is the JSON call. It may be EXCLUSIVELY what is stopping you 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");
}
+6
source to share
You can do the following,
-
Create a request with
[NSSting StringWithFormat:@"Insert Statement with Parameters"]
-
Store queries in an array.
-
Create a transaction. You can do it with SQL Query
-
Loop loop and query execution.
-
make a transaction
...
For each insert statement, sqlite starts a transaction and commits it. It's hard. To avoid such overhead, we can start our transaction. It's very fast.
0
source to share