Slow retrieval of data in SQLITE takes a long using ContentProvider
- by Arlyn
I have an application in Android (running 4.0.3) that stores a lot of data in Table A. Table A resides in SQLite Database. I am using a ContentProvider as an abstraction layer above the database.
Lots of data here means almost 80,000 records per month. Table A is structured like this:
String SQL_CREATE_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_A + " ( " +
COLUMN_ID + " INTEGER PRIMARY KEY NOT NULL" + "," +
COLUMN_GROUPNO + " INTEGER NOT NULL DEFAULT(0)" + "," +
COLUMN_TIMESTAMP + " DATETIME UNIQUE NOT NULL" + "," +
COLUMN_TAG + " TEXT" + "," +
COLUMN_VALUE + " REAL NOT NULL" + "," +
COLUMN_DEVICEID + " TEXT NOT NULL" + "," +
COLUMN_NEW + " NUMERIC NOT NULL DEFAULT(1)" + " )";
Here is the index statement:
String SQL_CREATE_INDEX_TIMESTAMP = "CREATE INDEX IF NOT EXISTS " + TABLE_A +
"_" + COLUMN_TIMESTAMP + " ON " + TABLE_A + " (" +
COLUMN_TIMESTAMP + ") ";
I have defined the columns as well as the table name as String Constants.
I am already experiencing significant slow down when retrieving this data from Table A. The problem is that when I retrieve data from this table, I first put it in an ArrayList and then I display it. Obviously, this is possibly the wrong way of doing things. I am trying to find a better way to approach this problem using a ContentProvider. But this is not the problem that bothers me.
The problem is for some reason, it takes a lot longer to retrieve data from other tables which have only upto 12 records maximum. I see this delay increase as the number of records in Table A increase. This does not make any sense. I can understand the delay if I retrieve data from Table A, but why the delay in retrieving data from other tables.
To clarify, I do not experience this delay if Table A is empty or has less than 3000 records.
What could be the problem?
