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)!

Is the content provider in the same process, or is it going cross process, the timing on that first row looks very odd. — superfell, Sep 16 '11 at 18:05

score 9 · Answer 1 · answered Sep 16 '11 at 17:39

9

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.

answered Sep 16 '11 at 17:39

superfell

18,780
4
59
81

score 3 · Accepted Answer · answered Sep 16 '11 at 19:03

3

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.

answered Sep 16 '11 at 19:03

CommonsWare

986,068
189
2,389
2,491

Thanks, it's more clear now! Given you are talking about Traceview, can you take a look at this question please? http://stackoverflow.com/questions/6491855/android-cant-use-traceview I just couldn't use it :( – Amokrane Chentir Sep 16 '11 at 23:34
@CommonsWare What would be your recommendation? What to do instead copying data to list of POJOs when we do raw queries? Thanks – Ewoks Apr 02 '15 at 06:37
@Ewoks: You alternative is to work straight with the `Cursor` (e.g., put it in a `SimpleCursorAdapter`). – CommonsWare Apr 02 '15 at 10:48

Looping through a SQLite Cursor takes too much time

2 Answers2