I have a table view and a sqlite db with around 1300 records. I load the data in some custom classes and then display the data in the tables.
In the simulator everything loads in about 2-3 seconds... on my real iPhone 2G it loads in around 17 seconds.
I just do 2-3 queries, the database is optimized, have 2-3 inner joins on table with 10-15 records... so why does it take so long on the real phone?
Any ideas? Should I try other approach in loading the data on the phone or am I doing something wrong?
Are you sure the queries are returning the results you want?
There is an extension for Firefox that will allow you to manage an sqlite database. I would download that and then use it to check your queries for the proper results before you put them into your app. The extension is SQLite Manager
Yes, I'm using that extension and the queries I run on iPhone, go around half a second in that firefox extension on my mac and also in 2-3 seconds on simulator.
Has anyone developed an application with a tableview that displays 1000+ rows for tableview or ran queries that returned a total of 1000+ records (objects)?
I have a table view and a sqlite db with around 1300 records. I load the data in some custom classes and then display the data in the tables.
In the simulator everything loads in about 2-3 seconds... on my real iPhone 2G it loads in around 17 seconds.
I just do 2-3 queries, the database is optimized, have 2-3 inner joins on table with 10-15 records... so why does it take so long on the real phone?
Any ideas? Should I try other approach in loading the data on the phone or am I doing something wrong?
You probably are doing something wrong... let me ask a few things to confirm.
Do you have indexes in your DB?
Bad/Inefficient way (slow for large data sets):
Are you trying to pre-process the all 1300 records into an array then displaying that array with a tableView?
Better/More efficient way for large data sets (lazy loading):
Or are you querying the db only when you need to display a row using LIMIT and OFFSET in your cellForRowAtIndexPath method. This is the proper way as it takes less time to run 7 or 8 queries (to get the data for the visible cells) than it does to select all your data, then init 1300 separate objects, add those objects to an array, then finally display the properties of those objects.
(SELECT * from data WHERE whatever = whatever LIMIT 1 OFFSET indexPath.row) something like that should be used in your cellForRowAtIndexPath method.
You might also have to run a few queries to determine the count of the results returned from your query so that the number of sections, or numberOfRows is known to the tableView as set in those methods.
Does this all make sense? I'm in a hurry but the same thing was happening to me until someone else pointed it out...
You probably are doing something wrong... let me ask a few things to confirm.
Do you have indexes in your DB?
Bad/Inefficient way (slow for large data sets):
Are you trying to pre-process the all 1300 records into an array then displaying that array with a tableView?
Better/More efficient way for large data sets (lazy loading):
Or are you querying the db only when you need to display a row using LIMIT and OFFSET in your cellForRowAtIndexPath method. This is the proper way as it takes less time to run 7 or 8 queries (to get the data for the visible cells) than it does to select all your data, then init 1300 separate objects, add those objects to an array, then finally display the properties of those objects.
(SELECT * from data WHERE whatever = whatever LIMIT 1 OFFSET indexPath.row) something like that should be used in your cellForRowAtIndexPath method.
You might also have to run a few queries to determine the count of the results returned from your query so that the number of sections, or numberOfRows is known to the tableView as set in those methods.
Does this all make sense? I'm in a hurry but the same thing was happening to me until someone else pointed it out...
Yes I have indexes in my DB.
I'm trying to load everything up in one array and the display everything... having a smoother scroll.
I've tried the second approach, but when I try to scroll faster I get lag moments of scrolling in my tableview which are annoying
I'm trying to load everything up in one array and the display everything... having a smoother scroll.
I've tried the second approach, but when I try to scroll faster I get lag moments of scrolling in my tableview which are annoying
Any conceptual solution?
I can note that loading everything into an array and displaying to a tableview is slow. I have one that is doing around 400 rows, it takes 3-5 seconds on an old 2G phone. Granted this is about a second or so on a new 3GS phone. And this is some pretty intense data, with some images in the database. Its just not the optimal way to display data....
I have fixed... have used BSDimwit idea and I'm doing queries in cellForRowAtIndexPath using OFFSET and LIMIT... the scrolling is not perfect, but it goes pretty fast.
I have fixed... have used BSDimwit idea and I'm doing queries in cellForRowAtIndexPath using OFFSET and LIMIT... the scrolling is not perfect, but it goes pretty fast.
Thank you very much guys for the help.
Could you post example code of how you accomplished this? Did you put the offset/limit inside cellForRowAtIndexPath; or somewhere else?
I have fixed... have used BSDimwit idea and I'm doing queries in cellForRowAtIndexPath using OFFSET and LIMIT... the scrolling is not perfect, but it goes pretty fast.
Thank you very much guys for the help.
I'm using Core Data to manage a database of some +4000 records, and it is very responsive. Core Data includes a NSFetchedResultsController that serves as a data source to a table view, and is optimized to fetch results in small batches.
Check out this password generator app that shows various techniques including using a data container singleton object to share data between objects in your project.
I use sqlite querying and all, even though the returned rows arent too big, around 50, I preload them as objects into an array and then reload table View. It does not make a big difference but for the sake of coding I would wana know more about the perfect way of lazy loading.
Couldnt find a proper way to do it thats smooth. Are there any examples?
I dont use CoreData, my queries are little complex and thats a reason why coredata scared me. Is it really worth switching for performance reasons??
I use sqlite querying and all, even though the returned rows arent too big, around 50, I preload them as objects into an array and then reload table View. It does not make a big difference but for the sake of coding I would wana know more about the perfect way of lazy loading.
Couldnt find a proper way to do it thats smooth. Are there any examples?
I dont use CoreData, my queries are little complex and thats a reason why coredata scared me. Is it really worth switching for performance reasons??
For my part, I haven't used SQLLite directly, and am pretty new with Core Data. I do know that it includes components that are highly optimized to serve as data providers to table views.
I can tell you that with almost no work the app I'm working on is able to scroll around in a list of 4000+ items with no lag.
I did a little looking around in the XCode docs on using SQL directly from iOS and couldn't find anything. Can you point me to some docs explaining how it's done?
Check out this password generator app that shows various techniques including using a data container singleton object to share data between objects in your project.
SQLite loading 1000+ records
Linear Mode
