Inserting to Sqlite is slow

Question

I am new to Database.

I am tying to insert 22000 word to a sqlite DB but it is taking forever to complete

for (int i=0;i<s.size();i++){               
    String sql = "INSERT INTO WORD (word) VALUES ("+"'"+s.get(i)+"'"+");";
    statement.executeUpdate(sql);
}

Table name is WORD and field name is word(which is a String) Here s is an arrylist and s.get(i) returns a string... I have made the connection and trying to update the table.

It is very slow. What am I doing wrong?

How many times to you insert 22 000 entries? What is "slow", as in, how long does it actually take? — Patrick, Nov 08 '14 at 15:38
It's in another programming language, but take a look at http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite — Patrick, Nov 08 '14 at 15:40
i don't know why but u can use enhanced loop.not so different — Madhawa Priyashantha, Nov 08 '14 at 15:41

vatsal · Answer 1 · 2014-11-08T16:47:52.547

1

Its slow because you are firing 22000 queries to the database. You should insert in batches. That will fix the problem. Even if a single insert takes 10ms total time is like 3.5 minutes!!

A simple insert query with batches of 2 will look like this

"INSERT INTO WORD (word) VALUES ("+"'"+s.get(i)+"'"+"), ("+"'"+s.get(i+1)+"'"+");";

This will reduce the time by half. Now you will have to programatically insert more of these values.

edited Nov 08 '14 at 16:47

answered Nov 08 '14 at 15:43

vatsal

3,803
2
19
19

1

"Might want to"? Are you guessing? Wouldn't it be better to also explain to the OP what a batch insert is, and how he would accomplish it? The very first sentence is "I am new to this", so there's a large possibility he doesn't know what a batch insert is. – Patrick Nov 08 '14 at 15:49
There is an option to edit the answer just in case my answer is insufficient. Thanks for pointing out the issues anyways il update it. – vatsal Nov 08 '14 at 15:56

Inserting to Sqlite is slow

1 Answers1