Bulk Insert into iphone sqlite database

Question

Hi i am using follwing code to insert bulk data from arrays into sqlite db...but it is extremely slow...please advise... Am I using the right approach?

sqlite3 *db1=nil; db1 = [One2GuideUAppDelegate getNewDBConnection];

sqlite3_stmt *init_statement = nil; @try { if(init_statement == nil) { const char *sql = "insert into tblBrand(intBId,intVersion,intLikes,intDontLikes,strName,strDesc,strWebsite,intEst,strFounder,strLogo,strThumbnail) values(?,?,?,?,?,?,?,?,?,?,?)"; if(sqlite3_prepare_v2(db1, sql, -1, &init_statement, NULL) != SQLITE_OK) NSAssert1(0,@"Error: Failed to prepare statement with message '%s'.",sqlite3_errmsg(db1)); } for(int br=0;br<[brands count];br++) { BrandVO *brandVO=[brands objectAtIndex:br ]; NSString *brandID=brandVO.brandID; NSString *brandVer=brandVO.brandVersion; NSString *brandLikes=brandVO.likes; NSString *brandDontLikes=brandVO.dontLikes; NSString *brandName=brandVO.name; NSString *brandDesc=brandVO.description; NSString *brandWeb=brandVO.url; NSString *brandEstb=brandVO.establishedYear; NSString *brandFounder=brandVO.founders; NSString *brandLogo=brandVO.logoURL; NSString *brandthumb=brandVO.thumbnailURL; sqlite3_bind_int(init_statement, 1, [brandID intValue]); sqlite3_bind_int(init_statement, 2, [brandVer intValue]); sqlite3_bind_int(init_statement, 3, [brandLikes intValue]); sqlite3_bind_int(init_statement, 4, [brandDontLikes intValue]); sqlite3_bind_text(init_statement, 5,[brandName UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_text(init_statement, 6,[brandDesc UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_text(init_statement, 7,[brandWeb UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_int(init_statement, 8,[brandEstb intValue]); sqlite3_bind_text(init_statement, 9,[brandFounder UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_text(init_statement, 10,[brandLogo UTF8String],-1,SQLITE_TRANSIENT); sqlite3_bind_text(init_statement, 11,[brandthumb UTF8String],-1,SQLITE_TRANSIENT); if(SQLITE_DONE != sqlite3_step(init_statement)) NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(db1)); sqlite3_reset(init_statement); } } @catch (NSException *ex) { @throw ex; } @finally { if(init_statement)sqlite3_finalize(init_statement); sqlite3_close(db1); }

Thanks,

omz · Accepted Answer · 2011-01-20 06:01:54Z

3

Wrap your insert statements in transactions with BEGIN TRANSACTION and END TRANSACTION. This usually speeds up bulk inserts a lot.

answered Jan 20, 2011 at 6:01

omz

53.6k5 gold badges133 silver badges141 bronze badges

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

4 Comments

devaditya Over a year ago

I guess we can not use the same(Begin and End transactions) while fetching data.... I am facing similar speed problem while fetching the data from the db... Please advise on it...

omz Over a year ago

Check if you have indexes for the columns you are querying.

devaditya Over a year ago

I havent created any indexes for the column i am specifying...I guess it is done at the time of database creation...How to go about it??

omz Over a year ago

Refer to sqlite.org/lang_createindex.html or use a GUI tool like SQLite Manager or Base (there are several).

Collectives™ on Stack Overflow

Bulk Insert into iphone sqlite database

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related