Android, SQLite: Inserting data in while clause is very slow

Question

SOLUTION AT THE BOTTOM OF THE QUESTION

I have a FTP Server with more than 3'000 pictures on it and I save the picture names to an ArrayList. After this I'd like to save them from arraylist to database and that is taking very very very very much time! See:

With

        FTPFile[] files = ftpClient.listFiles();

        for (FTPFile file : files) {
            imageNames.add(file.getName());
        }

I get a list of all those files and save them into the ArrayList imageNames. This is very fast and don't take more than 1 - maximum 2 seconds.

Now I like to write this array backwards to my sqlite database. When I do this it takes extremly long to be finished! I waited more than 2 minutes for it...

        int i = imageNames.size() - 1;
        int q = 0;

        while(i >= 0){
                String insert_arraylist = "INSERT INTO ARRAYLIST (STRING) VALUES ('"+imageNames.get(q)+"')";
                db.execSQL(insert_arraylist);
            i--;
            q++;
        }

So how can I speed up this inserting??

Thanks in advance!

SOLUTION: (From more than 2 minutes to less than 3 seconds ;))

        FTPFile[] files = ftpClient.listFiles();
        insert_arraylist = "";
        ContentValues con = new ContentValues();

        for (FTPFile file : files) {
            imageUrls.add("http://192.168.99.104/GetThePicture/thumbs/"+file.getName());
        }

        int i = imageUrls.size() - 1;
        int q = 0;

        db.beginTransaction();

        try{
            while(i >= 0){
                String insert_arraylist = "INSERT INTO ARRAYLIST (STRING) VALUES ('"+imageUrls.get(q)+"')";
                db.execSQL(insert_arraylist);
                i--;
                q++;
            }
            db.setTransactionSuccessful();
        } catch(Exception e){

        } finally {
            db.endTransaction();
        }

execSQL is not for SELECT/INSERT/UPDATE/DELETE – Selvin May 17 '13 at 12:31 — Selvin, May 17 '13 at 12:31

score 7 · Answer 1 · answered May 17 '13 at 12:35

The problem is that for every execSQL you are creating a new transaction and commiting. To resolve this problem, create a transaction and commit everything at once. Or you can create groups (eg. commiting for every 100 rows)

db.beginTransaction();
try {
    while(i >= 0){
        String insert_arraylist = "INSERT INTO ARRAYLIST (STRING) VALUES ('"+imageNames.get(q)+"')";
        db.execSQL(insert_arraylist);
        i--;
        q++;
    }
    db.setTransactionSuccessful();
} catch {
    // Handle exceptions.
} finally {
    db.endTransaction();
}

score 5 · Accepted Answer · edited May 23 '17 at 12:25

5

I would suggest to do batch inserts as explained in the following post:

Android SQLite database: slow insertion

It gives you multiple examples. Personally I have noticed that SQLite inserts are pretty slow, and for 3000 inserts its normal to take a long time unless you do batch inserts.

So idea is the same as the other poster posted, i.e. do it all in one transaction:

db.beginTransaction();
  for (entry : listOfEntries) {
  db.insert(entry);
}
db.setTransactionSuccessful();
db.endTransaction();

edited May 23 '17 at 12:25

Community

1
1

answered May 17 '13 at 12:38

zeeshan

4,913
1
49
58

Hmmm sounds not bad. But how can I convert an ArrayList to ContentValues? db.insert(String, String, ContentValues)...? – MSeiz5 May 17 '13 at 13:58
1

That's it! I'll post the working and VERY FAST code in top. It takes now less than 3 secondes for 3072 entries ;) – MSeiz5 May 17 '13 at 14:17
Wonderful and thanks for accepting this as your answer. I am glad you found your solution, I understand how good it feels :) – zeeshan May 17 '13 at 14:23

Android, SQLite: Inserting data in while clause is very slow

2 Answers2