Looping through a SQLite Cursor takes too much time

Question

I am using a SQLite database on an Android application and the getAll method that I have written takes too much time in my opinion.

This is the code I'm talking about:

public static List<Feed> getAll(Context context) {
        List<Feed> feeds = new ArrayList<Feed>();
        Uri allFeeds = Uri.parse(ContentProvidersUris.URL_CONTENT_PROVIDER_FEED);

        long startQuery = BenchmarkUtils.start();
        Cursor c = context.getContentResolver().query(allFeeds, null, null, null, "title desc");

        long startCursor = BenchmarkUtils.start();
        for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
            long startInsideCursor = BenchmarkUtils.start();

            Feed feed = new Feed();
            feed.setContent(c.getString(c.getColumnIndex(FeedsProvider.COL_WEBVIEW_CONTENT)));
            feed.setDate(c.getString(c.getColumnIndex(FeedsProvider.COL_PUB_DATE)));
            feed.setDescription(c.getString(c.getColumnIndex(FeedsProvider.COL_DESCRIPTION)));
            feed.setName(c.getString(c.getColumnIndex(FeedsProvider.COL_FEED_NAME)));

            Log.d(TAG, "This loop  cursor iteration took : " + BenchmarkUtils.stop(startInsideCursor) + " ms.");
        }

        Log.d(TAG, "Looping through the ENTIRE Cursor took: " + BenchmarkUtils.stop(startCursor) + " ms.");


        return feeds;
}

As you can see I am also measuring the time taken by this loop at each iteration and it turns out that it takes an average time of 1800 ms (on a Nexus S). I find that this is a lot of time. And what I don't understand is that most of this time is spent on the first iteration as shown in the logs:

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 1726 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 3 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 2 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 3 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 2 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 3 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 3 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 2 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 0 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 5 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 1 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 1 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 5 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 1 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 1 ms.

D/FeedsProviderHelper( 5800): This loop cursor iteration took : 1 ms.

D/FeedsProviderHelper( 5800): Looping through the ENTIRE Cursor took: 1770 ms.

So my questions are:

Is it normal? If yes, why? If not, what am I doing wrong? Any faster way to do a selectAll against the SQLite database?

Thanks!

EDIT

I took the getColumnIndex calls out of the loop as @superfell suggested, and now I am running the getAll method at an average 1500ms. It is faster but not fast enough in my opinion (again)!

Answer 1

The first thing you should do is take the getColumnIndex calls out of the loop, they're expensive, and you only need to do them once, not for every row.

Answer 2

Is it normal?

Sure.

If yes, why?

Disk I/O is expensive, particularly on flash. The query itself is executing on your first real request against the Cursor, which is why your first "iteration" is taking substantially longer.

Any faster way to do a selectAll against the SQLite database?

First, you're not doing a "selectAll" against a SQLite database. You're doing a "selectAll" against a content provider.

Use Traceview to determine precisely where your time is being taken up, and tune your application accordingly. For example, you might find that it makes more sense to not copy data from a Cursor into a list of POJOs in the first place.