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
//fields
private static final CharMatcher pat_rep = CharMatcher.inRange('A', 'Z').or(CharMatcher.inRange('a', 'z'))
.precomputed();
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;
//start
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) {
sent.add(in);
} else {
rcvd.add(in);
}
}
}
}
db.execSQL("create temp table if not exists WORDS (WORD varchar, SENT integer, CNT integer)");
SQLiteStatement ins = db.compileStatement("insert into WORDS values (?, ?, ?)");
db.beginTransaction();
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());
ins.executeInsert();
}
i = rcvd.entrySet().iterator();
while (i.hasNext()) {
e = i.next();
ins.bindString(1, e.getElement());
ins.bindLong(2, 0);
ins.bindLong(3, e.getCount());
ins.executeInsert();
}
db.setTransactionSuccessful();
db.endTransaction();
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);