Speed/expensive of SQLite query vs. List.contains() for "in-set" icon on list rows

An application I'm developing requires that the app main a local list of things, let's say books, in a local "library." Users can access their local library of books and search for books using a remote web service. The app will be aware of other users of the app through this web service, and users can browse other users' lists of books in their library. Each book is identified by a unique bookId (represented as an int).

When viewing books returned through a search result or when viewing another user's book library, the individual list row cells need to visually represent if the book is in the user's local library o开发者_如何学Pythonr not. A user can have at most 5,000 books in the library, stored in SQLite on the device (and synchronized with the remote web service).

My question is, to determine if the book shown in the list row is in the user's library, would it be better to directly ask SQLite (via SELECT COUNT(*)...) or to maintain, in-memory, a List or int[] array of some sort containing the unique bookIds.

So, on each row display do I query SQLite or check if the List or int[] array contains the unique bookId? Because the user can have at most 5,000 books, each bookId occupies 4 bytes so at most this would use ~ 20kB.

In thinking about this, and in typing this out, it seems obvious to me that it would be far better for performance if I maintained a list or int[] array of in-library bookIds vs. querying SQLite (the only caveat to maintaining an int[] array is that if books are added or removed I'll need to grow or shrink the array by hand, so with this option I'll most likely use an ArrayList or Vector, though I'm not sure of the additional memory overhead of using Integer objects as opposed to primitives).

Opinions, thoughts, suggestions?

First, for a pure memory solution I would probably use a HashSet<Integer> or HashMap<Integer>. It should give far better performance for contains/containsKey. Second, SQLite has its own memory caching, so you should not assume it will naively read from disk every time.

You are going to get alot of this: Write a quick test up and measure.

However, that said, an 'in memory' search will probably beat a 'on disk' search.

How many instances of your program are going to run 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 on-disk database. If you maintain an in-memory copy, you're going to need to worry about synchronization issues.

SQLite already provides significant caching. It is almost certain that the in-memory database will run faster. But here's an important question --- does it really matter?

You will also find it harder to debug a custom-written in-memory solution. If you use sqlite, you can use the command-line tool to help debug the database.