I'm currently working with a friend on developing a game that deals with words primarily, so I have a word list that I entered into a database. The plan is to have individual databases(as in on everyone's phone) be filled up during the installation. I have done every optimization I found on StackOverflow and Google, but it still takes too long, about almost 2 minutes, which is unacceptable for such a simple game.
The wordlist itself consists of around 350000 words, so we have created a file which contains the same number of inserts(is this the best way to go?), i.e each word has its own line in a text file with a corresponding insert. This is what I currently have:
public int initializeDatabase(Context context) throws IOException {
int result = 0; //returns querys completed
SQLiteDatabase db = this.getWritableDatabase();
//Opening a file for reading
InputStream insertStream = context.getResources().getAssets().open("allWords.txt");
BufferedReader insertReader = new BufferedReader(new InputStreamReader(insertStream));
//Iterating through lines
String query = "INSERT INTO words(word,tezina) VALUES(?,?)";//? entered in code
SQLiteStatement statement = db.compileStatement(query);
db.beginTransaction();//otvaram transakciju
while(insertReader.ready()){
statement.clearBindings();
String toExtract = insertReader.readLine();//uzimam upit
String word = "";
int tezina = (int)toExtract.charAt(toExtract.length() - 2);//extract word difficulty
for(int i = toExtract.length() - 4; i > 0; i--){
if(toExtract.charAt(i) == '(') break;
else word += toExtract.charAt(i);
}//extract word along with ' ', from back to save time
String reverseWord = "";
for(int j = word.length() - 1; j >= 0; j--){
reverseWord = reverseWord + word.charAt(j);
}//reverse it
statement.bindString(1, word);//bind parameters
statement.bindLong(2, tezina);
statement.execute();
result++;
}
db.setTransactionSuccessful();
db.endTransaction();//end of transaction
insertReader.close();
return result;//return queries completed
}
350262 inserts, line - by - line, taking about 2 minutes. Any better way of doing all of this?
