Creating 140,000-row SQLite database in AsyncTask doInBackground taking many, many minutes

Question

I haven't dealt with SQLite databases before last week. I last dealt with SQL many years ago, but I still have the gist of it.

The code below reads 140,000 words from an asset named dictionary.dic and inserts each into a SQLite database along with its status. My expectation was that it would take a good while, but it's been like 25 minutes on a 7" tablet and still not near finished (on P).

Should I say, "Hey, it's 1/7 of a million rows. It's gonna take awhile." But I can read all 140,000 words into an ArrayList<String> in 30 seconds. I realize there's overhead in creating the database, but many, many minutes?

Should I say, "Well, think how long it would take if not using AsyncTask" and accept it since it's a one-time task? But it's really obnoxious, taking so long. It's off-putting.

Should I say, "Why are you using a Scanner? No wonder it's taking so long?" and do some other asset access? Or is that not the real problem?

I also have never used AsyncTask. Am I misusing doInBackground? I've got a lot of code in there; not all MUST go there, but the loop is what it is and there's the hangup.

Is using database.Insert, which is called a "convenience method", what's causing the hangup? Should I be using a Cursor and query instead? I'm not entirely sure how I'd do that. Got my idea from Deitel's "Address Book" app in "Android for Programmers--App Driven...", but his database is empty at the outset.

I've given this plenty of thought. I just need someone with experience to look and say, "Well, HERE'S your problem." I can't justify starting redoing all the things I've thought of without some guidance about whether any of it is going to help.

public class DatabaseConnector //extends ArrayList<String>
{
  public static Cursor cursor ;
  Scanner scDict;
  InputStream stream = null;
  Context mContext;
  AssetManager mAssets;

  public static final String DATABASE_NAME      = "Dictionary";
  public static final String TABLE_NAME         = "wordlist";
  public static final String WORD_COLUMN_NAME   = "word";
  public static final String STATUS_COLUMN_NAME = "status";
  public static final String [] columns = new String[]{WORD_COLUMN_NAME, STATUS_COLUMN_NAME};

  private DatabaseOpenHelper ___databaseOpenHelper; // creates the database
  private SQLiteDatabase     ___database; // for interacting with the database

  public DatabaseConnector(Context _context, AssetManager assets)
  {
    mContext = _context;
    mAssets = assets;
    ___databaseOpenHelper = new DatabaseOpenHelper(_context, DATABASE_NAME, null, 1);
    Log.w("DB connected", ___databaseOpenHelper.getDatabaseName());
    createDbIfNecessary();
  };

  public void open() throws SQLException // opens/creates
  {
    ___database = ___databaseOpenHelper.getWritableDatabase();  // create OR open
  }

  public void createDbIfNecessary(){
    this.open();
    if(getDbCount() < 140000){
      try { stream = mAssets.open("dictionary.dic"); }

      catch (IOException e) { System.out.println(Arrays.toString(e.getStackTrace())); }

      MainActivity.setLblProgress("This one-time task takes awhile: loading letter... ");
        LoadWords loadWords = new LoadWords();
        loadWords.execute((Object[]) null);
      this.close();
    }
  }

  public void close(){
    if(___database != null)
       ___database.close();
  }

  public int getDbCount(){
    this.open();
    return ___database.query(TABLE_NAME, columns, null, null, null, null, null).getCount();

  }

   public long insertWord(String _word)
  {
    ContentValues
        __newWord;
    __newWord = new ContentValues();
    __newWord.put(WORD_COLUMN_NAME, _word);
    __newWord.put(STATUS_COLUMN_NAME, true);

      long __row = ___database.insert(TABLE_NAME, null, __newWord);

    return __row; // -1 if can't insert
  }

  //////////////////////////////////////////////////////////////////////////////////////////////////
  private class DatabaseOpenHelper extends SQLiteOpenHelper
  {
    public DatabaseOpenHelper(Context _context, String _name, CursorFactory _factory, int _version)
    { super(_context, _name, _factory, _version); }

    @Override public void onCreate(SQLiteDatabase _db)
    {
      _db.execSQL( "CREATE TABLE " + TABLE_NAME +
              "("
              + WORD_COLUMN_NAME   + " TEXT primary key , " //not null, "
              + STATUS_COLUMN_NAME + " BOOLEAN" +
              ");"
      ); // execute query to create the ___database
    }

  } // end class DatabaseOpenHelper
  //////////////////////////////////////////////////////////////////////////////////////////////////
  private class LoadWords extends AsyncTask<Object, Integer, Void>
  {
    @Override
    protected Void doInBackground(Object... params) {
      long k = 0;
      scDict = new Scanner(stream).useDelimiter("\r\n");
      long count = getDbCount();
      Log.w("Start load at " , "" + count);
      String s = "";
      while(k++ < count){
        s = scDict.next();
      }
      Log.w("Add after " , s);
      while (scDict.hasNext()) 
      {
        s = scDict.next();
        publishProgress((Integer)(int)s.charAt(0));
        insertWord(s) ;
      return null;
    }

    protected void onProgressUpdate(Integer... progress) {
      int c = (int)progress[0];
      MainActivity.setLastLetterProcessed((char) c);
    }

    @Override
    protected void onPostExecute(Void x)
    {
      MainActivity.popupMessage("Database has been created", mContext);
    }
  }
} // end class DatabaseConnector

Answer 1

You are attempting to do 140,000 individual database transactions. That might take weeks.

Instead, either wrap your entire thing in a single transaction, or batch the inserts into transactions (e.g., every 1000 words). You can create your own transaction bounds using this pseudo-Java:

db.beginTransaction();

try {
  // do your SQL work here
  db.setTransactionSuccesful();
}
catch (Exception e) {
  // logging, event bus message to UI, whatever
}
finally {
  db.endTransaction();
}

Answer 2

Thanks to @Commonsware, the 140,000 records now load in under a minute, as opposed to under an HOUR. All I did was use his "p-code" to surround my insert with a 1000-count loop:

    protected Void doInBackground(Object... params) {
      ...          
      scDict = new Scanner(stream).useDelimiter("\r\n");
      long count = getDbCount();
      while (k++ < count)
          s = scDict.next();

      while (scDict.hasNext())
      {
       //////////////////////////////////////////////////////// start insert
        int ki = 0;
        try
        {
          ___database.beginTransaction();

          while (ki < MAX_TRANSACTIONS && scDict.hasNext())
          {
       //////////////////////////////////////////////////////// end
             insertWord(scDict.next());
       //////////////////////////////////////////////////////// start insert
            ++ki;
          }
          ___database.setTransactionSuccessful();
        }
        catch(Exception e){ Log.w("Exception",e);}
        finally
        {
          ___database.endTransaction();
          publishProgress((Integer) (int) s.charAt(0));
        }
       //////////////////////////////////////////////////////// end
      }
      return null;
    }
    ...
  }