ios - database storing time takes long

Question

I am making an ios app which gets xml feed over the network, parse and store it in sqlite db, and then display to the user.

my xml feed is about 1700 lines long: http://www.paulshin.ca/yunatube/res/en.xml

This is how my app is supposed to work.

user runs the app
app gets xml over the network
use xml parser and loop through the xml object and store each item in the sqlite db
once it is done, display items in the tableview..

However, I found that step 2 takes less than 5 seconds whereas step 3 takes about 20 seconds. The reason is because it takes some time to store each item into database.

The following is what I use for step 3.

TBXMLElement *elemRoot = nil, *youtube = nil, *maincateg = nil, *categ = nil, *clip = nil;

    // maincatg
    NSString *mcTitle = nil;
    // catge
    NSString *cTitle = nil;
    // clip
    NSString *title = nil, *url = nil, *note = nil;

    elemRoot = tbxml.rootXMLElement;

    if (elemRoot) {
        youtube = [TBXML childElementNamed:@"youtube" parentElement:elemRoot];
        maincateg = [TBXML childElementNamed:@"maincateg" parentElement:youtube];

        while (maincateg) {
            mcTitle = [TBXML valueOfAttributeNamed:@"type" forElement:maincateg];

            categ = [TBXML childElementNamed:@"category" parentElement:maincateg];
            while (categ) {
                cTitle = [TBXML valueOfAttributeNamed:@"title" forElement:categ];

                clip = [TBXML childElementNamed:@"clip" parentElement:categ];
                while (clip) {
                    title = [TBXML valueOfAttributeNamed:@"title" forElement:clip];
                    url = [TBXML valueOfAttributeNamed:@"url" forElement:clip];
                    note = [TBXML valueOfAttributeNamed:@"note" forElement:clip];

                    //TODO
                    if (![note isEqualToString:@"0"]) {
                        // save pared data to persistent

                        title = [title stringByReplacingOccurrencesOfString:@"&amp;"
                                                                 withString:@"&"];
                        cTitle = [cTitle stringByReplacingOccurrencesOfString:@"&amp;"
                                                                 withString:@"&"];

                        DatabaseManager *database = [DatabaseManager getInstance];
                        **[database addDataToTable:mcTitle title:title url:url category:cTitle];**
                    }
                    clip = clip -> nextSibling;
                }
                categ = categ -> nextSibling;
            }

            maincateg = maincateg -> nextSibling;
        }

As you can see, I have some nested loops and I store each item to DB by calling addDataToTAble(); If I remove the line with *(in other words, if I don't store item after each loop), it works very fast. However, if I leave the line with * as it is, it takes so long time.

This function is as follows:

-(void) addDataToTable: (NSString*)table
             title: (NSString*)title
               url:(NSString*)url
          category:(NSString*)category
{
    sqlite3_stmt *statement;
    const char *dbpath = [_databasePath UTF8String];

    if (sqlite3_open(dbpath, &_yunatubeDB) == SQLITE_OK) {

        NSString *insertSQL = [NSString stringWithFormat: @"INSERT INTO %@ (TITLE, URL, CATEGORY) VALUES (\"%@\", \"%@\", \"%@\")", table, title, url, category];

        const char *insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(_yunatubeDB, insert_stmt,
                           -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE) {
            //            NSLog(@"Successfully added to DB");
        } else {
            NSLog(@"Failed to add data to DB");
        }
        sqlite3_finalize(statement);
        sqlite3_close(_yunatubeDB);
    }
}

It seems like this function takes a while when storing each item, and since the storing time accumulates, it eventually takes up to 20 seconds. I use similar algorithm for android and it works fast but I don;t know why it takes so long on ios. I wonder if this processing time might negatively affect the app review in the app store. I use similar method on android and it works very fast there.. less than 4 seconds. I don't know why it takes so long on ios. Am I doing something wrong ?

Any help is appreciated!

You can try to insert all rows in one INSERT sratent. See: http://stackoverflow.com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database — Benjamin Albert, Jun 23 '13 at 07:17
Open db everytime you insert? How about doing that just once before the whole loop? — Khanh Nguyen, Jun 23 '13 at 07:30

score 1 · Answer 1 · answered Jun 23 '13 at 06:36

1

Try starting a SQLite transaction before the loop and ending it afterwards. If you don't manually start/stop transactions, SQLite will do it automatically around every write, which is very slow.

answered Jun 23 '13 at 06:36

EricS

9,650
2
38
34

score 0 · Answer 2 · answered Jun 23 '13 at 06:37

0

20 seconds might pass apple review but will never get your app good reviews.

You're writing to db everytime you find an entry. You should write to db at the end of parsing.

answered Jun 23 '13 at 06:37

Tala

8,888
5
34
38

ios - database storing time takes long

2 Answers2