Search code examples
iossqliteuitableviewcore-datafmdb

Load visible cells in UITableView from SQLite database

I want to display several hundred records from a database in a UITableView. Though I could use CoreData, I decided to try using a SQLite database since I'm currently attending a lecture on databases. And although I possibly could load all of the data into an array, I want to minimize memory usage so that I can use sort of the same code for another application with more data as well. So my question is, how do I load the rows from the database which are currently displayed in the table view? The table I want to display has several attributes, the primary key is a string - the table should be sorted by this string. So how do I find out, what cells are displayed (indexes) and should I then use select * from mytable limit x,y to obtain the rows I want to display or can I improve performance in any way (either with SQLite or with some great code).

Thanks for your help in advance!

BTW: I'm using FMDB if this doesn't make any difference.

Solution

  • What you are trying to do is, in a way, absurd. NSFetchedResultsController which is part of Core Data, does all the optimizations for you exactly for displaying 100s of 1000s of rows in a table view.

    But as it seems you are doing this as an academic exercise, here are some hints:

    • Obviously, working with limits in the sql statement is the right approach.
    • You can fix the number of records you want in the array in memory to a fixed number, e.g. 50. This would depend on the maximum number of cells that fit on the screen, times 5 or so (for really fast scrolling).
    • You can still tell the table view in numberOfRowsInSection the total number of records. It will only request the records it needs to display.
    • The index of a row's record in the theoretical array of all records would be represented by indexPath.row.
    • You keep track of the current offset, representing the index in the total list of the top row in the table view.
    • You could write a function that gets the record for a certain index and checks if it needs to re-fetch.
    • You can get all visible cells of your table view with indexPathsForVisibleRows in case you need to check what is on the screen.
    • To make the scrolling smooth you need to override scrollViewDidScroll and do the proper calculations of contentOffset and set the table's contentOffset on the fly to reflect the correct part of the table.