Database insertion taking too much time ios sqlite

Question

I am trying to insert around 500 records into the sqlite database, it is taking 20 sec, which is way too much! Could anyone please tell how to optimize this.

for (int i = 0 ; i< [[[dic objectForKey:@"response" ]objectForKey:@"requete"]count]; i++) {
        [sqlManager executeQuery:[[[dic  objectForKey:@"response"]objectForKey:@"requetes"]objectAtIndex:i]];
    }

with

-(void)executeQuery:(NSString*)_query
{
sqlite3 *database;
if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {

    if(sqlite3_exec(database, [_query UTF8String],NULL, NULL, NULL) == SQLITE_OK){
        //NSLog(@"Query Success");
    }
    else{
       // NSLog(@"Query Failed");
    }
    sqlite3_close(database);
}
sqlite3_close(database);
}

Open only once. And then start a transaction if you're doing multiple inserts. — Hot Licks, Jan 21 '14 at 12:28
just a suggestion use FMDB https://github.com/ccgus/fmdb you can use transaction then without any worry of rollback — amar, Jan 21 '14 at 12:31
Note that **there is no need to open/close on every operation**. SQLite is quite robust in terms of preserving data if the app crashes with the DB open. Once you've done "commit" (which occurs implicitly after each op unless you start a transaction), the data is guaranteed to be written to disk. — Hot Licks, Jan 21 '14 at 12:39

score 1 · Answer 1 · edited May 23 '17 at 12:28

1

You are about to Open & closing the database for every Query(insertion) . So it takes time too.

example For inserting a multiple records at once.

edited May 23 '17 at 12:28

Community

1
1

answered Jan 21 '14 at 12:25

Kumar KL

15,315
9
38
60

Where did you start the transaction? – Hot Licks Jan 21 '14 at 12:30
And you leave the DB open if the prepare fails. – Hot Licks Jan 21 '14 at 12:32
@HotLicks Ok I just re-Posted my sample. That is only the example for reference . i can't provide the full solution Ok....? – Kumar KL Jan 21 '14 at 12:34
You still leave the DB open if the prepare fails. – Hot Licks Jan 21 '14 at 12:35
And a good illustration would not show opening/closing the DB at all (except possibly "lazy" opening), since it stupid to open/close for every operation. – Hot Licks Jan 21 '14 at 12:37
@HotLicks : Yes, I agree.. I just thought to give a quick overview of it . but it failed . – Kumar KL Jan 21 '14 at 12:51

score 1 · Accepted Answer · answered Jan 21 '14 at 12:45

1

Open once and leave open until the app is closed or you're done with a DB-intense phase. SQLite can easily handle the situation if your app "crashes" with the DB open.
When doing multiple inserts close together, where it's fine if they either all go or none of them go, start an transaction (and commit it when done). When you do not explicitly start a transaction then SQLite does an implicit start/commit around each insert or update, "forcing" the data to "disk". This takes A LOT of time. Doing one start/commit around a bunch of inserts will increase insert performance at least 10-fold.

answered Jan 21 '14 at 12:45

Hot Licks

47,103
17
93
151

I used your following solution it's work for me but I do one more thing in the background for an update some records which are coming from the server and it's `crash`. can you please give me a solution.? – Ilesh P Oct 13 '17 at 10:56
@ilesh - If you're doing stuff "in the background" you many not be serializing properly. – Hot Licks Oct 13 '17 at 12:13

score 0 · Answer 3 · answered Jan 21 '14 at 17:31

0

I think you must mantein your database open.

What takes more time is to open and close database.

For example, create a method only for that insertion, that opens the database in the firts element and closes it after the last.

answered Jan 21 '14 at 17:31

Miguel Chaves

139
1
9

Database insertion taking too much time ios sqlite

3 Answers3