Why is writing into an in-memory SQLITE database so much slower than using an ArrayList?

I maintain an application that is collecting a lot of information and is storing these information in an ArrayList.

In detail this ArrayList is defined as ArrayList<FileInformation> which has some member like:

private File mFile;
private Long mSize;
private int mCount;
private Long mFilteredSize;
private int mFilteredCount;
private int mNumberOfFilters;

etc.

This approach is working but is not very flexible when I would like to introduce some new functionality. It also has some limitations in terms of memory usage and scale-ability. Because of this I did some tests if a database is the better approach. From the flexibility there is no question, but somehow I'm not able to make it running fast enough to become a real alternative.

Right now the database has just one table like this:

CREATE TABLE ExtContent (
                        "path" TEXT not null, 
                        "folderpath" TEXT not null, 
                        "filename" TEXT,
                        "extention" TEXT,
                        "size" NUMERIC,
                        "filedate" NUMERIC,
                        "isfolder" INTEGER not null,
                        "firstfound" NUMERIC not null,
                        "lastfound" NUMERIC not null,
                        "filtered" INTEGER not null
                       );

The performance issue is immense. Collecting and writing ~14000 items takes ~3mins! when writing into the database and just 4-5secs if written into the ArrayList. Creating the database in-memory does not make a big difference.

As my experience in terms of SQLITE is rather limited, I started by creating the entries via the android.database.sqlite.SQLiteDatabase.insert methode.

As there was no meaningful difference between a file based and a in-memory database, I guess using BEGIN TRANSACTION and COMMIT TRANSACTION will not make any difference.

Is there some way to optimize this behavior?

Just for clarification, putting BEGIN TRANSACTION and END TRANSACTION will increase the performance greatly. Quoted from http://www.sqlite.org/faq.html#q19 :

SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. By default, each INSERT statement is its own transaction...

I had a similar issue on an app I was coding on the weekend.

Is the data in the database to be included in the app when it's released? If so, bulk inserts aren't they way to go, instead you want to look at creating the database and including it in the assets directory and copying it over to the device. Here's a great link.

Otherwise I'm not sure you can do much to improve performance, this link explains methods on bulk inserting into an SqlLite Database.

Edit: You may also want to post your insert code too.

This is opretty obvious. Assuming you already allocated object to insert into. ( This is the same workload for bot solutions ) Let's compare alternatives:

• Inserting in ArrayList does: - (optional) allocate new chinks of cells for pointers if necessary - insert object pointer into array list on the end ... really fast
• INserting into sqlite: -prepare insertion query ( I hope you use prepared query, and do not construct it from strings) -perform database table insertion with modifications of indexes etc. ... a lot of work

Only advantage of database is that you can: - query it later - it handles external storage transparently allowing you to have much more entities But it comes at cost of performance.

Depending on what you are for, there could be better alternatives.

For example, in my android games I store highscore entries in JSON file and utilise GSON Pull parser / databinding layer ( https://github.com/ko5tik/jsonserializer ) to create objects out of it. Typical load time for 2000 entries from external storage is about 2-3 seconds