Sqlite performance with 10000 of records in iPad

Question

I'm using FMDB wrapper class for my iPad application. I'm having table which contains nearly 10000 records. Each record is having 140 fields. I'm using Modal class to store the retrieved values like,

 NSString *query = [NSString stringWithFormat:@"select * from table"];
 FMResultSet *results = [db executeQuery:query];
 while([results next]) {
    ModalClass *modal = [[ModalClass alloc] init];
    [modal setField1:value1];
    [modal setField2:value2];
    [modal setField3:value3];
    [modal setField4:value4];
    .
    .
    .
    .
    .
    [modal setField139:value139];
    [modal setField140:value140];

    [array addObject:modal];
 }

I've used some options i know, performSelectorOnMainThread, dispatch_async() and some other multithreading techniques. But nothing helped me to make this efficient.

In Simulator, it takes 5 seconds. But when it comes to device it takes nearly 20 seconds.

Database is given by client, so i could not change or modify any tables.

Can anyone help me to make this efficient. Waiting for 20 seconds is very disgusting.

Thanks.

And why do you need all 10,000 rows in memory? You're not doing it right... — trojanfoe, Feb 26 '13 at 12:08
But you can't realisticly view them in even an iPad - you need to cut down the number of rows the user scrolls over — mmmmmm, Feb 26 '13 at 12:19
I doubt that all 1,400,000 values fit on the screen at once. You should load only those values that you actually need. — CL., Feb 26 '13 at 12:19
yeah @CL, i didn't think in that way. I'll try. Thanks for your help. — arthankamal, Feb 26 '13 at 12:28

score 2 · Accepted Answer · answered Feb 26 '13 at 12:26

2

You are approaching the problem in the wrong way. The UITableView will have a data source that is called to allow you to populate the cells. Use these methods to load only the row data you need, not all 10,000 rows up front.

I normally use a simple caching method to avoid re-loading the same row over and over for different cells within the tableview, but you don't need to worry about that initially (worry about it later if you feel the database is being hit too hard).

answered Feb 26 '13 at 12:26

trojanfoe

120,358
21
212
242

I agree: this is the way to go. Not only for speed-performance, there are also memory usage considerations. – Rok Jarc Feb 26 '13 at 12:28
@trojanfoe. you mean, you want me to take only the values what i'm displaying in Tableview. – arthankamal Feb 26 '13 at 12:38
2

Yes, as mentioned by @rokjarc, iOS uses this approach anyway so that the data for each row is supplied only when required, in order to cut down the memory footprint. Getting the row from the database only when required follows this principle. – trojanfoe Feb 26 '13 at 12:46

Sqlite performance with 10000 of records in iPad

1 Answers1