CODEWITHSUNDEEP
androidsqlite
Kaidul
Kaidul

Reputation: 15855

How to make SQLite select query faster

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?

Upvotes: 1

Views: 5886

Answers (4)

Geobits
Geobits

Reputation: 22342

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.

Upvotes: 4

Reactgular
Reactgular

Reputation: 54741

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.

Upvotes: 1

Emil Davtyan
Emil Davtyan

Reputation: 14089

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

Upvotes: 0

CL.
CL.

Reputation: 180020

Create an index on the problem_id column.

Upvotes: -1

Related Questions