0

I have been playing with different ways of inserting 5000 records into sqlite like using execSql & insert methods on SQLiteDatabase object and compiled statements. In all the cases the inserts (actually insert or replace) are made inside a single transaction.

When using compiled statements the average insertion time is approximately 3000 milliseconds for 5000 records. In case of insert() method the average time hovers around 4500 milliseconds and for execSql() it is approximately 5000 milliseconds.

I was expecting sqlite to be a lot faster. In the official website, it is stated that on an average, a desktop computer can handle 50,000 inserts per second (in a transaction). Even though the test device I am using isn't a particularly powerful one (Galaxy S3 mini), I expected the SSD storage to offset any disadvantages and provide ~20,000 inserts per second. Alas!! That doesn't seem to be the case.

Am I doing something wrong or is this the max performance that can be eked out of a mobile device?

EDIT 1

All the records have 14 columns and they are a mixture of text & numbers (most are uuids or urls)

Cœur
  • 37,241
  • 25
  • 195
  • 267
x-treme
  • 1,606
  • 2
  • 21
  • 39
  • Desktop computer is very much powerful than mobile phone in general. Mobile phone has cpu with slow-clock-speed and small cache memory, amount of memory is very limitted (few handreds of MB is available in mobile phone, and few GB is available in desktop) and memory clock is slow. – Fumu 7 Oct 21 '14 at 02:06
  • http://stackoverflow.com/questions/14344172/android-bulk-insert-when-inserthelper-is-deprecated – Vny Kumar Oct 21 '14 at 04:56
  • @Fumu7 - never thought that the difference would be this big!! – x-treme Oct 21 '14 at 06:24
  • @VnyKumar - the answer from your link is one of the 3 methods I used – x-treme Oct 21 '14 at 06:25
  • so create new thread (like `AsyncTask`) then exec transaction. i dont know what is 3 methods u said, but transaction is common way i think. – ytRino Oct 21 '14 at 06:53
  • Mobile devices do not have SSDs, they have crappy flash chips that are as bad as cheap USB stick. – CL. Oct 21 '14 at 10:53

1 Answers1

-1

if I remember correctly，insert operation takes a lot of time. Maybe it's not a bad choice to try concurrent database transaction.Here is what i wrote before:

                    db.beginTransaction();
                    while (...) {
                        db.execSQL(...));
                    }
                    db.setTransactionSuccessful();
                    db.endTransaction();
Mark Shen
  • 346
  • 1
  • 5
  • But this is not concurrent database transaction. this is just wrapping up the statements inside a transaction which I am actually doing in the all the 3 ways I have listed above – x-treme Oct 21 '14 at 06:21
  • x-tream: Don't complain without real code. Every body answered/commented by guess because you don't show your code. – Fumu 7 Oct 21 '14 at 07:28
  • @Fumu7 "inside a single transaction" is right there in the question. – CL. Oct 21 '14 at 10:55