Efficient table views using SQLite

Discussion in 'iOS Programming' started by (marc), Mar 21, 2011.

  1. (marc), Mar 21, 2011
    Last edited: Mar 21, 2011

    (marc) macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #1
    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)?
     
  2. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #2
    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 ;)
     
  3. (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #3
    Well, I'm trying to make most of the code run on non-Apple systems as well :)
     
  4. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #4
    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 :D
     
  5. (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #5
    (Should be) home turf! :D
     
  6. (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #6
    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?
     
  7. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #7
    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.
     
  8. (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #8
    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.
     
  9. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #9
    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..)

    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.
     
  10. (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #10
    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?
     
  11. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #11
    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"
     
  12. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #12
  13. (marc), Mar 22, 2011
    Last edited: Mar 22, 2011

    (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #13
    Note: Implementing scrolling using LIMIT and OFFSET is wrong. That's why I used BETWEEN, but probably incorrectly.
     
  14. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #14
    http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
    Ok, that's pretty bad... :D

    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.
     
  15. (marc), Mar 22, 2011
    Last edited: Mar 22, 2011

    (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #15
    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...
     
  16. dantastic macrumors 6502

    dantastic

    Joined:
    Jan 21, 2011
    #16
    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.
     
  17. (marc), Mar 22, 2011
    Last edited: Mar 22, 2011

    (marc) thread starter macrumors 6502a

    (marc)

    Joined:
    Sep 15, 2010
    Location:
    the woods
    #17
    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?
     

Share This Page