Agfdhyk

I have the same query executing over and over and sometimes it is ultra quick, other times it takes much longer. Any one know what could be causing this?

I have a Content Provider that is accessed via a loader on my activity.

It has to filter through 10,000 plus entries into a database and I had optimized it significantly to reduce query time.

Often the query runs in 0-30 ms. As I print out info every time the query executes.

But I have noticed now with more data, sometimes the query will run super quick, and other times it will take 3-5 seconds. Using the same selection/query/arguments as the fast execution.

Log is printing out the times below: And the query is exactly the same.

Query took 15ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 15ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 3499ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 3502ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 18ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 6ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 3618ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

Query took 3601ms to complete SELECTION: Rows: 3 :: type != ? sel args: [0]

I record this above log as such in the Provider

queryBuilder.setProjectionMap(projectionMap);

long start = System.currentTimeMillis();

retCursor = queryBuilder.query(mOpenHelper.getReadableDatabase(), null,

                selection,

                selectionArgs,

                null,

                null,

                null, null);

//This goes with the log statement, to prevent a null pointer on selectionArgs

if (selectionArgs == null) {

    selectionArgs = new String{"null"};

}

Log.d(LOG_TAG, "Query took " + (System.currentTimeMillis() - start) + "ms to complete SELECTION:" +

        " Rows: " + retCursor.getCount() +

        " :: " + selection + " sel args: " + Arrays.toString(selectionArgs));

Edit: The table I am pulling from has half a million rows. But I put a column in with a flag to indicate if it was the last record inserted for a specific user. And indexed it. This allowed me to get a row for each users last position really quickly. A new position removes the flag on the old position and inserts the new position with the flag set. I can query the table in less than 20ms, or the ones that seem to get stuck at about 2.5 seconds or more. which seems to happen about 30-40% (est) of the time now.