我希望能够加速一个在我的SQLite数据库中进行的相当简单的查询操作。该查询操作是在大约6500行数据的表格中进行的,目前返回结果需要大约600毫秒。我不知道这是否可以接受,但我原本希望它会更快。最终我会将其放入AsyncTask中,但我想尽可能地进行优化。在下面的代码片段中,您可以看到我已经尝试过的内容。请注意,KEY_songPath已被索引。
public ArrayList<Song> getCurrentDirSongs(String currentDir) {
long startTime = System.currentTimeMillis();
ArrayList<Song> songsList = new ArrayList<Song>();
String selectQuery = "";
int strLength = currentDir.length();
//selectQuery = "SELECT * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " >= '" + currentDir + "')";
//selectQuery = "SELECT * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " GLOB '" + currentDir + "*')";
//selectQuery = "SELECT * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " LIKE '" + currentDir + "%')";
selectQuery = "SELECT * FROM " + TABLE_SONGS + " WHERE " + KEY_ID +
" IN (SELECT " + KEY_ID +" FROM "+ TABLE_SONGS + " WHERE length(" + KEY_songPath + ")>= "+ strLength +") AND " +
"(" + KEY_songPath + " LIKE '" + currentDir + "%')";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Song song = new Song();
song.setSongTitle(cursor.getString(1));
song.setSongPath(cursor.getString(2));
song.setSongArtist(cursor.getString(3));
song.setSongAlbum(cursor.getString(4));
song.setTrackNumber(cursor.getString(5));
song.setID(Integer.parseInt(cursor.getString(6)));
song.setAlbumID(Integer.parseInt(cursor.getString(7)));
song.setArtistID(Integer.parseInt(cursor.getString(8)));
song.setSongCheckedStatus(Boolean.parseBoolean(cursor.getString(9)));
song.setSongPosition(Integer.parseInt(cursor.getString(10)));
song.setSongDuration(Integer.parseInt(cursor.getString(11)));
// Adding song to list
songsList.add(song);
} while (cursor.moveToNext());
}
long estimatedTime = System.currentTimeMillis() - startTime;
Log.d("AMCDatabase Database Handler: ", "SongsList.size(): "+ getSongsCount("SongsList") + " Time: " + estimatedTime + "ms");
// return contact list
return songsList;
以下是返回的结果: SongsList.size():6390 时间:588毫秒
非常感谢你的帮助!
附言:这可能不是问题 - 在查询和将6000个值添加到ArrayList
中,一秒钟的时间是否可接受?
long startTime = System.currentTimeMillis();
移到执行rawQuery之前来确认这一点。此外,你应该考虑在getCurrentDirSongs()之外的早期时间只执行一次SQLiteDatabase db = this.getReadableDatabase();
。 - Suraj C