Databases
StackOverflow2013
Postsdbo
POAndroid SQLite Select query speed
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAndroid SQLite Select query speed
    primarykey
    data
    text
    <p>I would like help speeding up a fairly simple select query in my <code>SQLite</code> database. </p> <p>The query is searching a table of approx 6500 rows and currently returns in about 600ms. I don't know if that is acceptable or not, but I had hoped that it would be faster than that. I will be putting this into an <code>AsyncTask</code> eventually, but wanted to optimize as much as possible. In the code snippet below you can see what I have tried.<br> KEY_songPath is indexed.</p> <pre><code>public ArrayList&lt;Song&gt; getCurrentDirSongs(String currentDir) { long startTime = System.currentTimeMillis(); ArrayList&lt;Song&gt; songsList = new ArrayList&lt;Song&gt;(); String selectQuery = ""; int strLength = currentDir.length(); //selectQuery = "SELECT * FROM " + TABLE_SONGS + " WHERE (" + KEY_songPath + " &gt;= '" + 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 + ")&gt;= "+ 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; </code></pre> <p>}</p> <p>This has returned with the following result: SongsList.size(): 6390 Time: 588ms</p> <p>Any help gratefully received! </p> <p>P.S This may all be a non-issue - is that time of a second acceptable to query and 6000 values to an <code>ArrayList</code>?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMr.Adam
    1. USMr.Adam
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAndroid SQLite Select query speed
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAndroid SQLite Select query speed
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POAndroid SQLite Select query speed
      1. USeebygum
      1. VTFavorite
    1. COMaybe I'm missing something, but what's wrong with your third commented query statement? I'm not sure I understand why you're querying for KEY_ID and then querying again based on that.
      singulars
      1. POAndroid SQLite Select query speed
      1. USGeobits
    2. COHi Geobits. I was just messing around trying to speed it up. Query 3 and 4 return in similar times. The idea was to create a smaller subset of the table
      singulars
      1. POAndroid SQLite Select query speed
      1. USMr.Adam
    3. COAh. The reason I asked is that in your nested query, you're running the `LIKE currentDir%` part on the full 6k rows anyway, so I can't see how it would be an improvement over the third one. Either way, like you said in your update, it may be a non-issue. It's hard to say what an acceptable time is, though. Especially if you're going to go async with it(which you should), what is acceptable to you? I'd worry about getting it all done first. Speed comes later, if it's needed. It's probably not the query, though. Check the timer after the query and before the loop to see what you get.
      singulars
      1. POAndroid SQLite Select query speed
      1. USGeobits
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload