Staggering sqlite records with Objective C

Question

An iphone app we are producing will potentially have a table with 100,000+ records (scaling wise) and I am concerned that loading all this data via a SELECT * command into an array, or an Object will either make the app very slow; or will leave me with memory issues later on.

Obviously loading 100,000+ records into an array when the viewscreen/viewport only shows 30 odd records in a go is a tad silly.

In addition, I am not sure if storing all this data in an object is the right thing to do either.

So my question is, is it possible to stagger sqlite through records, say 50 records in cache and then when you swipe down or up it loads an appropriate amount into the cache. I guess its similar to the JQuery Lazy Loading library where it only loads a little bit on the view port and then loads more as you move down.

I'm looking at JSON, but it appears to only be for Web services as it requires a URL and I am not sure if it works for files that are on the phone.

Therefore. Is there a proper way to load sqlite data into Objective C arrays/objects without causing problems when the data suddenly starts to scale?

Thanks for your help.

Answer 1

You definitely want to avoid loading in everything at once. Instead, you want to use a cursor and smarter queries so that you only pull data out of the database as you actually need it for display; that link points to some hints on how to do that.

Generally, you also want to avoid blindly selecting all columns. This is because you may sometime update the schema to add a column that your existing code would not always know how to extract; far better to name the explicitly columns that you expect and desire.