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?