I'm trying to run a rather large set of Update queries on a SQLite database in Android and feel like there's some way to improve the performance over what I'm currently doing, but I'm not sure what else I can improve. Currently, I'm grabbing data from another table in my SQLite database and looping through this cursor to update fields in another table. This is my loop right now (its all done under one transaction):
for (int i = 0; i < c.getCount(); i++) {
buildUpdateQuery(stringBuilder, c);
Cursor tmpCursor = dbHandler.rawQuery(query, null);
tmpCursor.moveToFirst();
dbHandler.closeCursor(tmpCursor);
onProgressUpdate(new Pair<>(PROGRESS_TEXT, i));
c.moveToNext();
}
dbHandler.closeCursor(c);
my buildUpdateQuery method does the following:
queryBuilder.append("UPDATE ");
queryBuilder.append(TABLE_NAME);
queryBuilder.append(" SET ");
queryBuilder.append(COL1);
queryBuilder.append(" = ");
queryBuilder.append(c.getLong(3));
queryBuilder.append(", ");
queryBuilder.append(COL2);
queryBuilder.append(" = ");
queryBuilder.append(c.getInt(2));
queryBuilder.append(" WHERE ");
queryBuilder.append(INDEXED_COLUMN1);
queryBuilder.append(" = ");
queryBuilder.append(c.getLong(1));
queryBuilder.append(" AND ");
queryBuilder.append(INDEXED_COLUMN2);
queryBuilder.append(" = ");
queryBuilder.append(StringUtil.StringQuote(c.getString(0))); //wraps string in single quotes and escapes quotes in string
my onProgressUpdate method looks like:
public void updateProgress(final Pair<String, Integer>[] values) {
runOnUiThread(new Runnable() {
@Override
public void run() {
progressBarText.setText("Adjusting " + values[0].second + " records");
}
});
}
This loop takes about 15 minutes to complete. The cursor in my test case is for about 6200 records and each update query might modify 7-8 rows in the other table each time. Also of note, I did originally have this in one UPDATE query. That may have improved the time by a little bit, but gave me no feedback on progress so I switched it to the loop above so I could at least print some progress updates to the user. So is there any obvious bottlenecks in my code or some way to improve the speed of this loop? Or am I just SoL on this.
Aucun commentaire:
Enregistrer un commentaire