CODEWITHSUNDEEP
androidsqliteandroid-contentprovider
Charon
Charon

Reputation: 67

Query executing on large data set in loader is slow/causing UI to freeze/stutter

I have a query that gets a MAX(time) from a table with GPS positions containing 300,000 entries. I have the time indexed as well as the USER,TIME indexed.

I access the positions in a loader that gets notified every time a new position is inserted into the table, and will refresh the loader information thus calling the query

But my problem is nearly every time this happens the UI will lock up until the query is finished.

I know this because I added a Log.d to the provider to display how many ms the query takes to execute every time it is executed. Sometimes the query will execute in 30ms (ish) but most of the time it will take 2000-4000ms sometimes up to 6000ms. And during this time the UI will stop responding.

I have three questions,

  1. Isn't the use of the loader supposed to decouple the UI slowing down in this manner so that a long query wont cause the UI to lock up while it is executing?
  2. And would there be anything that would cause the query to wait for a "lock" that would cause it do delay its executing making the query take longer?
  3. Why does the query seem to change in execution speed sometimes really quick, but most of the time quite slow.

I have tried simplifying the query in the provider to the following just for testing:

queryBuilder = new SQLiteQueryBuilder();
tables = PositionEntry.TABLE_NAME;
queryBuilder.setTables(tables);
projectionMap = new HashMap();
projectionMap.put(PositionEntry.COLUMN_USER, PositionEntry.COLUMN_USER);          
projectionMap.put(PositionEntry.COLUMN_TIME,"MAX("+PositionEntry.COLUMN_TIME + ") AS " +PositionEntry.COLUMN_TIME );
queryBuilder.setProjectionMap(projectionMap);

long start1 = System.currentTimeMillis();
retCursor = queryBuilder.query(mOpenHelper.getReadableDatabase(), null, null, null, PositionEntry.COLUMN_USER, null, null, null);
Log.d(LOG_TAG,"Sub Query took " + (System.currentTimeMillis() - start1) + "ms to complete Rows:" + retCursor.getCount());

I get 3 rows back which is correct, and I get the last position for each user based on their last time. But the query takes 2 seconds or so to execute each time.

//Edit: Fixed a code typo

Upvotes: 0

Views: 173

Answers (1)

MikeT
MikeT

Reputation: 57043

Perhaps consider having a single row table in which the max time is automatically updated via a trigger when a row is inserted into the main table.

Assuming a table named lastentry with columns lastentry_id (INTEGER but not an alias of rowid) and lastentry_time (INTEGER for the max time) and that the master/main table is called tracker with the id column and time column (plus others)

Then the following

CREATE TRIGGER trg_update_lastentry 
AFTER INSERT ON tracker 
BEGIN  
    UPDATE lastentry 
        SET 
            lastentry_id = new._id, 
            lastentry_time = new.tracker_time 
    WHERE lastentry_time < new.tracker_time
    ; 
END

would update the single row in the lastentry table (i.e. change the lastentry_time column and the lastentry_id column (reference to the ID of the respective tracker table row)) if the time in the tracker table is greater than the time in the lastnetry table.

Thus you could extract the max time without having to query the 300,000 row main (tracker) table.

Testing/Example

DBHelper.java :-

public class DBHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "traking";
    public static final int DBVERSION = 1;

    public static final String TB_TRACKER = "tracker";
    public static final String COL_TRACKER_ID = BaseColumns._ID;
    public static final String COL_TRACKER_USER = "tracker_user";
    public static final String COL_TRACKER_TIME = "tracker_time";
    public static final String COL_TRACKER_LATTITUDE = "tracker_lattitude";
    public static final String COL_TRACKER_LONGITUDE = "tracker_longitude";

    public static final String TB_LASTENTRY = "lastentry";
    public static final String COL_LASTENTRY_ID = "lastentry_id";
    public static final String COL_LASTENTRY_TIME = "lastentry_time";

    public static final String TRG_UPDATE_LASTENTRY = "trg_update_lastentry";



    SQLiteDatabase mDB;


    public DBHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase();
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        // tracker(main) table
        String crttrackersql = "CREATE TABLE IF NOT EXISTS " + TB_TRACKER +
                "(" +
                COL_TRACKER_ID + " INTEGER PRIMARY KEY," +
                COL_TRACKER_USER + " TEXT," +
                COL_TRACKER_TIME + " INTEGER NOT NULL," +
                COL_TRACKER_LATTITUDE + " INTEGER," +
                COL_TRACKER_LONGITUDE + " INTEGER" +
                ")";
        db.execSQL(crttrackersql);

        // lastentry (max time) table - just has 1 row
        String crtlastentrysql = "CREATE TABLE IF NOT EXISTS " + TB_LASTENTRY +
                "(" +
                COL_LASTENTRY_ID + " INTEGER," +
                COL_LASTENTRY_TIME + " INTEGER" +
                ")";
        db.execSQL(crtlastentrysql);


        //Adds the initial, to be update entry in the lastentry table
        String initlastentry = "INSERT INTO " + TB_LASTENTRY + " VALUES(0,0)";
        db.execSQL(initlastentry);

        //Define the Trigger equivaent of :-
        /*
            CREATE TRIGGER trg_update_lastentry 
            AFTER INSERT ON tracker 
            BEGIN  
                UPDATE lastentry 
                    SET 
                        lastentry_id = new._id, 
                        lastentry_time = new.tracker_time 
                WHERE lastentry_time < new.tracker_time
            ; 
            END

         */
        String crtlastentryupdate = "CREATE TRIGGER IF NOT EXISTS " + TRG_UPDATE_LASTENTRY +
                " AFTER INSERT ON " + TB_TRACKER +
                " BEGIN " +
                " UPDATE " + TB_LASTENTRY +
                " SET " + COL_LASTENTRY_ID + " = new." + COL_TRACKER_ID +
                ", " + COL_LASTENTRY_TIME + " = new." + COL_TRACKER_TIME +
                " WHERE " + COL_LASTENTRY_TIME + " < new." + COL_TRACKER_TIME +
                ";" +
                " END";
        db.execSQL(crtlastentryupdate);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    //Used to delete/ redefine the entire Database
    public void restructureDB() {
        String droptracker = "DROP TABLE IF EXISTS " + TB_TRACKER;
        mDB.execSQL(droptracker);
        String droplastentry = "DROp TABLE IF EXISTS " + TB_LASTENTRY;
        mDB.execSQL(droplastentry);
        String droplastentryupdate = "DROP TRIGGER If EXISTS " + TRG_UPDATE_LASTENTRY;
        mDB.execSQL(droplastentryupdate);
        onCreate(mDB);
    }

    //Insert a tracker table entry using current time
    public long insertTracker(String user, long lattitude, long longitude) {
        return insertTrackerWithTime(user,lattitude,longitude, System.currentTimeMillis());
    }

    //Insert a tracker table entry specifying the time
    public long insertTrackerWithTime(String user, long lattitude, long longitude, long time) {
        ContentValues cv = new ContentValues();
        cv.put(COL_TRACKER_TIME, time);
        cv.put(COL_TRACKER_USER,user);
        cv.put(COL_TRACKER_LATTITUDE,lattitude);
        cv.put(COL_TRACKER_LONGITUDE,longitude);
        return mDB.insert(TB_TRACKER,null,cv);
    }
}

MainActivity.java (testing) :-

public class MainActivity extends AppCompatActivity {

    DBHelper mDBHelper; // declare DBHelper

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        // Instantiate mDBHelper
        mDBHelper = new DBHelper(this);

        // Insert some rows with various times (noting highest time is not last)
        mDBHelper.insertTracker("Fred",100,100); // NOW
        mDBHelper.insertTrackerWithTime("Bert",110,220,System.currentTimeMillis() - (1000 * 60 * 60 * 24));// Less 1 day
        mDBHelper.insertTrackerWithTime("Bert",110,220,System.currentTimeMillis() - (1000 * 60 * 60 * 24 * 5)); // less 5 days
        mDBHelper.insertTrackerWithTime("Henry",110,220,System.currentTimeMillis() + (1000 * 60 * 60 * 24)); // tomorrow
        mDBHelper.insertTrackerWithTime("Bert",110,220,System.currentTimeMillis() - (1000 * 60 * 60 * 24 * 2)); // less 2 days
        mDBHelper.insertTrackerWithTime("Bert",110,220,System.currentTimeMillis() - (1000 * 60 * 60 * 24 * 7)); // less 1 week

        // get all the inserted rows
        Cursor csr = mDBHelper.getWritableDatabase().query(
                DBHelper.TB_TRACKER,
                null,
                null,
                null,
                null,
                null,
                null
        );
        // Shows the rows in the log
        while (csr.moveToNext()) {
            Log.d("TRACKER",
                    "User = " + csr.getString(csr.getColumnIndex(DBHelper.COL_TRACKER_USER)) +
                            "Time = " + csr.getString(csr.getColumnIndex(DBHelper.COL_TRACKER_TIME))
            );
        }
        // get all the lastentry table rows (1)
        csr = mDBHelper.getWritableDatabase().query(
                DBHelper.TB_LASTENTRY,
                null,
                null,
                null,
                null,
                null,
                null
        );
        // log the values
        while (csr.moveToNext()) {
            Log.d("MAXTIME", "Maximum Time is " + csr.getString(csr.getColumnIndex(DBHelper.COL_LASTENTRY_TIME)) +
                    " for ID = " + csr.getString(csr.getColumnIndex(DBHelper.COL_LASTENTRY_ID))
            );
        }
    }
}

Result :-

04-19 00:44:17.037 1645-1645/? D/TRACKER: User = FredTime = 1524098657028
    User = BertTime = 1524012257031
04-19 00:44:17.041 1645-1645/? D/TRACKER: User = BertTime = 1523666657033
    User = HenryTime = 1524185057036
    User = BertTime = 1523925857039
    User = BertTime = 1523493857041
04-19 00:44:17.041 1645-1645/? D/MAXTIME: Maximum Time is 1524185057036 for ID = 4

i.e. the maxtime is 1524185057036 which reflects the 4th row of the 6 rows.

Upvotes: 1

Related Questions