This is a follow-up of my question here:

I am currently working on an application for various statistics. One task is to analyse a good amount of sentences for their word counts.

The specifications are:

  • sentences are read from SQLiteDatabase (up to 20k with an average of about 15 words)
  • transformation: split by whitespaces (to get the words of the sentences)
  • transformation: toLowerCase (to minimize variations of words)
  • transformation: replace [^a-zA-Z] (for the same reason as above)
  • get word + count for the first x (not sure yet, maybe 10-15) most common words
  • preserve a flag if the messages was sent/received

What I'm looking for:

  • improvements to make the code run faster
  • alternative approaches for this task
  • (general hints to improve the task)

Current version with the suggested improvements made


private static final CharMatcher pat_rep = CharMatcher.inRange('A', 'Z').or(CharMatcher.inRange('a', 'z'))
private static final Pattern pat_split = Pattern.compile("\\s");
private HashMultiset<String> sent = HashMultiset.create();
private HashMultiset<String> rcvd = HashMultiset.create();
private Cursor c1;
private Cursor c2;


    c1 = db.rawQuery("select lower(DATA) as SENTENCE, SENT from MESSAGELIST", null);
    while (c1.moveToNext()) {
        String[] words = pat_split.split(c1.getString(c1.getColumnIndex("SENTENCE")));
        int from_me = c1.getInt(c1.getColumnIndex("key_from_me"));
        for (String in : words) {
            in = pat_rep.retainFrom(in);
            if (!in.equals("")) {
                if (from_me == 1) {
                } else {

    db.execSQL("create temp table if not exists WORDS (WORD varchar, SENT integer, CNT integer)");
    SQLiteStatement ins = db.compileStatement("insert into WORDS values (?, ?, ?)");

    Iterator<Entry<String>> i = sent.entrySet().iterator();
    Entry<String> e = null;
    while (i.hasNext()) {
        e = i.next();
        ins.bindString(1, e.getElement());
        ins.bindLong(2, 1);
        ins.bindLong(3, e.getCount());
    i = rcvd.entrySet().iterator();
    while (i.hasNext()) {
        e = i.next();
        ins.bindString(1, e.getElement());
        ins.bindLong(2, 0);
        ins.bindLong(3, e.getCount());

    c1 = db.rawQuery("select WORD, CNT from WORDS where SENT=0 group by WORD order by CNT desc limit 10", null);
    c2 = db.rawQuery("select WORD, CNT from WORDS where SENT=1 group by WORD order by CNT desc limit 10", null);

1 Answer 1


Just some ideas.

Maybe you could use batching?

You may be also able to save some time by iterating over sent.entrySet() instead of looking up the count separately.

Split on [^a-zA-Z] as you later throw non-letter away anyway.

Can't you use JDK5 loops like

for (String in : send) {...}

? I guess, clearBindings is unnecessary as you always overwrite everything.

Make all fields private. Always (unless you have a very good reason not to). AT least I hope that pat_rep etc. are fields.

Split your method. Shorter methods are easier to read and to optimize.

  • \$\begingroup\$ entrySet, JDK5 loops, no clearBindings(), some field changes - got me another improvement by 0.89 (~2s for the sample case)! code is updated. Is batching possible for SQLite? And would it replace the usage of a transaction? I can´t quite combine these Methods. \$\endgroup\$
    – Maze
    Aug 20, 2014 at 16:23
  • \$\begingroup\$ @MazePutze I don't know much about batching. I've never used SQLLite, just UTFG. I'd bet it's no replacement for transactions. I can't see why you couldn't do both. \$\endgroup\$
    – maaartinus
    Aug 20, 2014 at 16:45
  • \$\begingroup\$ @MazePutze Btw., there's a poor man's batching, too, assuming, the DB supports it: insert into WORDS values (?, ?, ?), (?, ?, ?) or even more tuples at once. It makes the code rather ugly. But before doing anything, I'd measure, what code part takes the longest. \$\endgroup\$
    – maaartinus
    Aug 20, 2014 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.