What is the fastest way to load 10000 rows from the SQLite database?

Question

What is the fastest way to load 10,000 rows from a SQLite database into memory? Each row has 1 text and 4 integers. Currently, I'm doing this:

while(!cursor.isAfterLast()) {
    cursor.copyStringToBuffer(column_index_1, buffer);
    cursor.copyStringToBuffer(column_index_2, buffer);
    cursor.copyStringToBuffer(column_index_3, buffer);
    cursor.copyStringToBuffer(column_index_4, buffer);
    cursor.copyStringToBuffer(column_index_5, buffer);
    cursor.moveToNext();
}

The above code takes about 750 ms on Galaxy Nexus. On older devices it could be couple of times slower. Can I make it faster?

(At this point, you're probably typing something like "Why do you need to load the rows into memory?" It's a bit complicated but I can try to explain if someone's interested. Edit: here's the explanation: https://gist.github.com/fhucho/af355d56ae3145e3e30f)

Stagnant

I'm interested! Please explain. :) – Mellissamellitz

@KenWolf ok, I'll post a link to explanation after couple minutes :) – Stagnant

If all columns of a row are copied into the same buffer it may be worth a try to create an SQL statement like "select col1 || col2 || ..." to let sqlite do the concatenation. This may or may be not faster. – Ebullition

@KenWolf some things are simplified a bit, if something's not clear, I can explain: gist.github.com/fhucho/af355d56ae3145e3e30f – Stagnant

Answer 1

First i advice you to load only the first 1000 rows, and then Use AsyncTask to load more.

You can find response here http://www.javasrilankansupport.com/2012/11/asynctask-android-example-asynctask-in.html

Hope this help

Wealthy

Well, all of the rows should be loaded asynchronously. It's not like the first 1,000 rows are free. :-) – Devoir

I'll do it almost certainly asynchronously, but that wouldn't change the fact, that it would be slow :) – Stagnant