iOS Efficient table views using SQLite

(marc) · Mar 21, 2011

Hi,

I'm writing a simple todo app (for the fun of it, not for production). The app is pretty much a big UITableView displaying entries from a database. For educational purposes, I do not want to use Core Data but sqlite3 directly. When I display the table view, should I:

read all database entries into a big array when the app starts (very inefficient!)
only load data for visible rows (i.e. rows 361-372) from the database and reload those values when the user scrolls (using scrolling cursors as described here)
?

I'd really like approach 2 but how practical is it (especially during fast-scrolling scenarios)?

dantastic · Mar 21, 2011

I know you said for educational purposes but if you were to use core data and just selected the navigation based template with core data in xcode all this work is done for you, and done really well.

In the fetched results controller you'd specify a batch to load, it's something like 20 in the template. that way you're not running a new query for each cell and you don't need the entire data set in memory either.

If this is educational I wouldn't load everything onto one array - what's the fun in that?? I would try to replicate the behaviour of the fetched results controller.

So if this is purely educational check out how the fetched results controller works - if not, use core data and a fetched results controller

(marc) · Mar 21, 2011

dantastic said:
[...]
So if this is purely educational check out how the fetched results controller works - if not, use core data and a fetched results controller

Well, I'm trying to make most of the code run on non-Apple systems as well

dantastic · Mar 21, 2011

Right, fair enough.

In that case load a batch of results as you scroll. If you load 20 or so at the time there should be no issues with performance.
If you keep your data already indexed inthe DB in the order you want to display it it should be a very simple task of matching the indexpath.row against the index in your DB.

To make this super cool you could do like a flappy paddle gear box. You have a second array as well. As soon as you have loaded the first array with a batch of 20 you load a second array with the next 20 - or if scrolling up - the previous 20.
That way the gear is already engaged and you just need to change the array over. For this to be truly effective you'd have to do this in a separate thread though. (But I recon the DB is fast enough that you won't notice anyway...) Now we're entering purely academic territory though

(marc) · Mar 21, 2011

dantastic said:
[...]
Now we're entering purely academic territory though

(Should be) home turf!

(marc) · Mar 22, 2011

dantastic said:
[...]
If you keep your data already indexed inthe DB in the order you want to display it it should be a very simple task of matching the indexpath.row against the index in your DB.
[...]

I don't quite understand indexing. In my table, I already have a column "primaryKey" but in my table view, I want to sort the data by a date (stored in the table as an integer). How does indexing come into play here?

dantastic · Mar 22, 2011

What I meant there was that if you keep the date formatted in your database such as

Code:

PrimaryKey	reminderTitle	date
0			reminder 1		2/3/11
1			other rem		4/1/11

then you can just go indexPath.row = primaryKey. Would have been very simple but you are responsible for the order and that there are no gaps in the index yourself.

If you want to sort on date you'd have to (well, don't HAVE to) also index the date column (you do that on the DB itself). then you can run a query against your table, sort on date and only return objects within a certain range. You can still get the range from indexPath.row.

I don't quite understand indexing.

Indexing is essentially a pre-sort. You keep info about the sort order of a table. - when you, through a SQL query, ask the DB to sort the result, the DB can either churn though the entire table and sort the rows according to your request (costly) - Or refer to the index for the column you have requested a sort on.

Indexing will cost storage overhead but it will save hardware resources.

(marc) · Mar 22, 2011

dantastic said:
What I meant there was that if you keep the date formatted in your database such as

Code:

PrimaryKey reminderTitle date 0 reminder 1 2/3/11 1 other rem 4/1/11

then you can just go indexPath.row = primaryKey. Would have been very simple but you are responsible for the order and that there are no gaps in the index yourself.

If you want to sort on date you'd have to (well, don't HAVE to) also index the date column (you do that on the DB itself). then you can run a query against your table, sort on date and only return objects within a certain range. You can still get the range from indexPath.row.

Indexing is essentially a pre-sort. You keep info about the sort order of a table. - when you, through a SQL query, ask the DB to sort the result, the DB can either churn though the entire table and sort the rows according to your request (costly) - Or refer to the index for the column you have requested a sort on.

Indexing will cost storage overhead but it will save hardware resources.

Since I'm storing dates as integers, isn't the date column "indexed" already? Or does indexing really require the sequence of natural numbers? Also, how would I "sync" the primaryKey column with the date information?
Sorry for all those stupid questions, as you can see I'm very new to SQL.

dantastic · Mar 22, 2011

Since I'm storing dates as integers, isn't the date column "indexed" already? Or does indexing really require the sequence of natural numbers?

nope and nope. Only the promary key is indexed by default - if you want to index anything else you will have to tell the DB about it when you first create the table.
You can index on what ever you want. If you want to index giraffes the DB will do it for you. (The result may not be what you thought... but..)

Also, how would I "sync" the primaryKey column with the date information?

You wouldn't - you'd index the date column instead.
What you are thinking of there is re-shuffling the rows which would be very costly.

(marc) · Mar 22, 2011

dantastic said:
[...]
You wouldn't - you'd index the date column instead.
What you are thinking of there is re-shuffling the rows which would be very costly.

OK, so from reading this I gathered I would index my dueDate table column like so:

Code:

CREATE INDEX dueDateIndex ON todos (dueDate ASC)

Then, to select, I'd use something like:

Code:

SELECT * FROM todos WHERE dueDateIndex BETWEEN 5 AND 10

... which would select the 5th to 10th "most due" todos. Is this correct?

dantastic · Mar 22, 2011

So your query would probably be something like

Code:

SELECT date, note FROM todos WHERE 1 = 1 ORDER BY duedate LIMIT 20 OFFSET 60

to return 20 rows of the date & note columns. we'll be returning result 61-80 in the sort order of dueDate.

You may want to try the above in sqlplus against your instance. I've not done anything against sqlplus so the syntax may be a small bit off...
You may not need "where 1 = 1"

dantastic · Mar 22, 2011

actually, have a look at http://www.sqlite.org/lang_select.html - it's all pretty well explained down the bottom.

(marc) · Mar 22, 2011

dantastic said:
So your query would probably be something like

Code:

SELECT date, note FROM todos WHERE 1 = 1 ORDER BY duedate LIMIT 20 OFFSET 60

to return 20 rows of the date & note columns. we'll be returning result 61-80 in the sort order of dueDate.

You may want to try the above in sqlplus against your instance. I've not done anything against sqlplus so the syntax may be a small bit off...
You may not need "where 1 = 1"

Note: Implementing scrolling using LIMIT and OFFSET is wrong. That's why I used BETWEEN, but probably incorrectly.

dantastic · Mar 22, 2011

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

The way OFFSET works in SQLite is that it causes the sqlite3_step() function to ignore the first :index breakpoints that it sees. So, for example, if :index is 1000, you are really reading in 1005 entries and ignoring all but the last 5.

Ok, that's pretty bad...

That very page has the solution though:

Code:

//As the display is initialized, you must remember the first and last title 
//being displayed in separate variables outside of SQLite. To scroll down,
// run this query:

    SELECT title FROM tracks
     WHERE singer='Madonna'
       AND title>:lasttitle
     ORDER BY title
     LIMIT 5;

//To scroll up, run this query:

    SELECT title FROM tracks
     WHERE singer='Madonna'
       AND title<:firsttitle
     ORDER BY title DESC
     LIMIT 5;

That being said you may be somewhat limited if you are sorting on date. The given implementation would never support an arbitrary number of todos per day (if you are sorting on dates). You would have to stick in another unique field there as well that you can search on. It will cost a bit more but since offset is out you've no choice if you don't want to have a fixed upper limit of todos per day.

(marc) · Mar 22, 2011

dantastic said:
[...]

Why would that implementation not support an arbitrary number of todos per day? I would have thought that creating an index for dueDate would be enough. At that point, when scrolling down, I can remember the dueDate of the todo furthest in the future, discard the current batch of todos and load a new batch using something like:

Code:

SELECT * FROM todos WHERE dueDate < :lastDueDate ORDER BY dueDate ASC LIMIT 20

EDIT: Got the code working. Scrolling is perfectly smooth (although that's not necessarily true for older devices than an iPhone 4). The big problem is that todos with the same dueDate can appear not at all or multiple times in the table view...

dantastic · Mar 22, 2011

Your just using the index to sort your data, your querying a date. To be more exact:

Code:

SELECT * FROM todos WHERE dueDate < 20110322 ORDER BY dueDate ASC LIMIT 20

In the above, dueDate = 20110321 is your start and you'll get the following 20 results matching that query. So if there are more than 20 results your last date will be the same as your first date, therefore you won't scroll anywhere. (And if you would limit yourself to 20 results your scrolling would be jumpy.)

This approach works if dueDate is unique, not otherwise.

In order to utilize the type of scrolling you are after you need to be able to run your query against a column you're pretty sure of will be unique. (considering, you don't need to guarantee uniqueness...)

If you can't sort the data using a unique column you will have to go back to my previously suggestion solution of using offsets. Costly as it may be, it will work with your current design.

(marc) · Mar 22, 2011

dantastic said:
[...]
In order to utilize the type of scrolling you are after you need to be able to run your query against a column you're pretty sure of will be unique. (considering, you don't need to guarantee uniqueness...)
[...]

Well, isn't the index of the dueDate column unique? Plus, it has the correct ordering so that would really be ideal. Isn't there some way to use it? I'd really like to utilize efficient scrolling. What solution would you propose?

Search

Search

iOS Efficient table views using SQLite

(marc)

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

(marc)

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

dantastic

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

dantastic

macrumors 6502a

(marc)

macrumors 6502a

Our Staff