Speed / expensive SQLite query and List.contains () for "in-set" icon in list rows
An application I'm developing requires the application to contain a local list of things, say books, in a local "library". Users can access their local book library and search for books using a remote web service. The app will know about other users of the app through this web service, and users can view other users' lists of books in their library. Each book is uniquely identified bookId
(represented as int
).
When viewing the books returned by a search result, or when viewing another library of custom books, the individual cells of the list row should visually represent whether the book is in the user's local library or not. A user can have a maximum of 5000 books in the library stored in SQLite on the device (and sync with a remote web service).
My question is to determine if the book given in the list string is in a custom library, it would be better to ask SQLite right away (via SELECT COUNT(*)...
) or store in memory List
or an int[]
array containing a unique bookId
s.
So, on each row display, I am querying SQLite or checking if an array contains List
or is int[]
unique bookId
? Since a user can have no more than 5000 books, each bookId
takes 4 bytes, so at most this will use ~ 20kB.
If we think about it and typing this, it seems obvious to me that it would be much better for performance if I maintain a list or int [] array inside the library bookId
vs. SQLite query (the only caveat for int [] array support is that if books are added or removed I will need to increase or decrease the array manually, so with this option I will most likely use ArrayList
or Vector
, although I'm not sure about additional memory consumption for using objects Integer
as opposed to primitives).
Opinions, thoughts, suggestions?
a source to share
First, for a pure memory solution, I would probably use HashSet<Integer>
or HashMap<Integer>
. This should give much better performance for contains
/ containsKey
. Second, SQLite has its own memory caching , so you shouldn't assume that it will naively read from disk every time.
a source to share
How many instances of your program will be running at the same time? An important advantage of using SQLite is that multiple copies of your program will get the same result if they all access the same database on disk. If you keep a copy in memory, you have to worry about synchronization issues.
SQLite already provides significant caching. The in-memory database will almost certainly run faster. But here's the big question: does it really matter?
You will also find it difficult to debug a custom solution in memory. If you are using sqlite you can use a command line tool to help debug the database.
a source to share