How to make SQLite select query faster

Question

Hello I am pretty new with SQLite and I am trying to deal with some database manipulation in my project.

I have a table with almost 4000 rows and this is the format of every row:

problem_id (string)
problem_no (string)
problem_title (string)
dacu (int)

I need to query a bunch of problem_no based on the problem_id. The quantity of query is almost 1000 at a time. So I wrote a query code like this:

Set<Integer> getProblemsTitle(HashSet<String> problemsIDs) {
        SQLiteDatabase db = this.getReadableDatabase();
        HashSet<Integer> problemNo = new HashSet<Integer>();
        Cursor cursor = null;
        for (Iterator<String> iterator = problemsIDs.iterator(); iterator.hasNext();) {
            cursor = db.query(CommonUtils.PROBLEM_TABLE, new String[] {
                    CommonUtils.KEY_PROBLEM_NO },
                    CommonUtils.KEY_PROBLEM_ID + "=?",
                    new String[] { iterator.next() }, null, null, null, null);
            if (cursor != null && cursor.moveToFirst()) {
                problemNo.add(cursor.getInt(0));
            }
            cursor.close();
        }
        db.close();
        Set<Integer> set = new TreeSet<Integer>(problemNo);
        return set;
    }

I know this is not a optimized snippet. And I need to optimize it a lot to reduce the execution time of the query. I did it inside AsyncTask but it is taking too much time.

How can I do this efficiently with faster performance?

This isn't how I would look up titles for an associated record. I would use a `LEFT JOIN` so that when you query problems you also get the problem titles. I'd think you need to clarify where `problemsIDs` comes from. — Reactgular, Aug 06 '13 at 19:47

score 4 · Accepted Answer · edited May 23 '17 at 11:44

4

You might want to consider taking this out of the database. If you just grabbed all the problems, you could add them all in code. Running one SELECT with 4000 results is still going to be much faster than a thousand SELECT statements.

The approach would be to grab them all, but sorted(ORDER BY problem_id). You could then just check each item in problemIDs against it, and add when you get a match.

You could also use the IN operator as Mathew suggests, but I don't know how efficient that will be with 1000 items in the set.

edited May 23 '17 at 11:44

Community

1
1

answered Aug 06 '13 at 13:46

Geobits

22,218
6
59
103

I missed the part about a 1000 ids. – Reactgular Aug 06 '13 at 19:46
@Geobits and @Mathew Foscarini, I need to do a lot of single item query like this. So should I query all the database and map in `hasMap`? It will 4000 map insertion operation and each map insertion operation will take `O(1)`. Would it be efficient? – Kaidul Aug 07 '13 at 04:39
1

You can't control where the user stores your app. If they put it on an SD card then read/write speeds will kill performance. If the data is mostly static, then either preload it all or cache it in a map. You will do two things 1) save performance 2) save battery. Loading data from storage on Android isn't good for battery life. – Reactgular Aug 07 '13 at 11:11

score 1 · Answer 2 · answered Aug 06 '13 at 13:41

1

Don't iterate over a collection of IDs, but use the IN operator in a WHERE condition.

SELECT * FROM Table WHERE problem_id IN (1,2,3,4,5)

This will return all the records in the set. Whereas you are querying them one at a time.

answered Aug 06 '13 at 13:41

Reactgular

52,335
19
158
208

Thanks for your answer. This seems to be faster. `This will return all the records in the set. Whereas you are querying them one at a time.` Can you give me some working example with full code? I am new in SQLite – Kaidul Aug 06 '13 at 17:41
I don't do much SQLite. Sorry, maybe this will help. http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ – Reactgular Aug 07 '13 at 11:13

score 0 · Answer 3 · answered Aug 06 '13 at 13:38

0

You could try compiling a query, and maybe you can try to load the database into memory before reading.

answered Aug 06 '13 at 13:38

Emil Davtyan

13,808
5
44
66

score -1 · Answer 4 · answered Aug 06 '13 at 13:35

-1

Create an index on the problem_id column.

answered Aug 06 '13 at 13:35

CL.

173,858
17
217
259

How to make SQLite select query faster

4 Answers4