View Full Version : How improve scrolling with +5000 records sqlite table

Apr 7, 2009, 09:48 PM

I have a sqlite database similar to the Apple contacts app.

I do all the tricks I know to get a smoth operation, but I have a problem.

I load the data in 50 recods blocks. Then, when the user scroll, request next 50 until finish the list.

However, load that 50 records cause a notable "pause" in loading & scrolling. Everything else work fine.

I cache the data, have opaque cells, draw it by code, etc...

This is the code:

//This load the records with LIMIT 50
NSArray *list = [db loadAndFill:sql theClass:[self returnItemClass]];

//This fill the biz objects. Here are the main wait...

-(NSArray *) loadAndFill: (NSString *)sql theClass: (Class)cls {
[self openDb];

NSMutableArray *list = [NSMutableArray array];

NSAutoreleasePool *pool = [[NSAutoreleasePool alloc] init];

id ds;
Class myClass = NSClassFromString([DbObject getTableName:cls]);

FMResultSet *rs = [self load:sql];

while ([rs next]) {
ds = [[myClass alloc] init];

[self fill:ds resultset:rs];

[list addObject :ds];

[ds release];
[rs close];

[pool drain];
return list;

I wonder what to do. Maybe using a async loading? or threads?

No using objects and put the data in a array?

Apr 8, 2009, 02:16 AM
It looks like you know what you're doing but as you're seeing the biggest slow down on loading the next 50 maybe you should reduce the size of the blocks down to maybe 20? I don't know how complex your cells are....are you precompositing them into a single view (this probably isn't your problem though).

Apr 8, 2009, 04:42 AM
I wonder what to do. Maybe using a async loading? or threads?

If you're not already doing your database/list load in a background thread then you definitely should be. Take a look at performSelectorInBackground:withObject: (https://developer.apple.com/iphone/library/documentation/Cocoa/Reference/Foundation/Classes/NSObject_Class/Reference/Reference.html#//apple_ref/occ/instm/NSObject/performSelectorInBackground:withObject:).

In order to do updates to the UI you'll have to call the main thread from the background thread (the UI ignores all threads but the main), for which you'll need performSelectorOnMainThread:withObject:waitUntilDone: (https://developer.apple.com/iphone/library/documentation/Cocoa/Reference/Foundation/Classes/NSObject_Class/Reference/Reference.html#//apple_ref/occ/instm/NSObject/performSelectorOnMainThread:withObject:waitUntilDone:).

Backgrounding your database accesses will prevent the main (UI) thread being blocked, so should make your app much more responsive (i.e. it should remove the pause you're seeing now).

Apr 8, 2009, 10:59 AM
You should spend some time optimizing your sql and profiling and optimizing the code that runs while scrolling. You should definitely try different chunk sizes down to five or less to find the best size for good scrolling speed. Smaller may be better.

Do those things before you look at threading this code. Threads will make this whole process much more complicated.

Apr 8, 2009, 12:12 PM
Do those things before you look at threading this code. Threads will make this whole process much more complicated.

I put it to 5 (the # of visible cells) and the lagh is there anyway (obviously is less than with 50).

I don't see where focus the optimization. The cells are drawing with drawRect.

I will investigate the use of threads then...

Apr 8, 2009, 01:08 PM
I triying with NSOperation:

- (void) run:(NSString *)sql {
NSArray *data = [NSArray arrayWithObjects:
[self returnItemClass],

NSInvocationOperation *operation =
[[NSInvocationOperation alloc] initWithTarget:self
[self.queue addOperation:operation];
[operation release];

- (void) loadRecords:(NSArray *)data {
NSLog(@"Runing sql");
NSString * sql = [data objectAtIndex:0];
Class cls = [data objectAtIndex:1];

Db *db= [Db currentDb];

NSArray *list = [db loadAndFill:sql theClass:cls];
[UIAppDelegate performSelectorOnMainThread:@selector(recordsLoaded:)

- (void) recordsLoaded:(NSArray *)data {
NSLog(@"Loading sql");
for (DbObject *o in data) {
[self.objectCache setObject:o forKey:[NSNumber numberWithInt:o.Id]];

But I'm getting "Terminating app due to uncaught exception 'NSInvalidArgumentException', reason: '*** -[JhonSellAppDelegate recordsLoaded:]: unrecognized selector sent to instance"

Any idea in how code this rigth?