title: Speeding up DB queries on Android
body: <p>I'm using SQLite on Android using SQLiteDatabase (<a href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html" rel="nofollow">http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html</a>)</p>
  
  <p>I am developing a bible application, which has a single table with the following columns:</p>
  
  <ul>
  <li>book : int</li>
  <li>chapter : int</li>
  <li>verse : int</li>
  <li>wordIdx : int</li>
  <li>strongId : string</li>
  <li>word : string</li>
  </ul>
  
  <p>each sentence is broken down in to a series of strongId/word pairs, so wordIdx is used to order the words, strongId is simply a index in to a concordance, and word is the word in the sentence.</p>
  
  <p>so I have 300,000 rows
  the bottleneck appears to be my query to get a list of words for each verse:</p>
  
  <p>My SQL is effectively this:</p>
  
  <pre><code>SELECT strongId, word FROM ? WHERE book=? AND chapter=? AND verse=?
  </code></pre>
  
  <p>Here is the code:</p>
  
  <pre><code>Cursor cursor = mBible.database().rawQuery("SELECT " + KEY_STRONGID + "," + KEY_WORD + " FROM " + tableName() + " WHERE " + KEY_BOOK + "=? AND " + KEY_CHAPTER + "=? AND " + KEY_VERSE + "=?" , new String[] { String.valueOf(mChapter.mBook.index()), String.valueOf(mChapter.index()), String.valueOf(verse) });
  
  cursor.moveToFirst();
  
  mWordList = new ArrayList&lt;Word&gt;();
  do {
      mWordList.add(new Word(cursor.getString(1), cursor.getString(0)));
  } while (cursor.moveToNext());
  </code></pre>
  
  <p>Now, I've tried putting each chapter in to its own temporary view (using CREATE TEMP VIEW) which cuts down the records to about 400 in my example how ever it is still taking far to long to query</p>
  
  <p>Its taking of the order of 30 seconds to generate the text for two chapters to display to the user (using a temporary view and without using a temporary view). It takes about 5 seconds if I set up a dummy list of words to avoid the database query.</p>
  
  <p>How can I improve the performance of this? It seems as if a temp view is having no impact on performance as I had hoped.</p>