At I/O last week, Google announced which makes working with SQLite databases a lot easier, but it begs the question: How well does it perform? a new tool called Room This is a followup to my . First we’ll look at some results from a couple of experiments comparing the relative performance of Room against the practices discussed in the previous post. Then, I’ll provide some of my own suggestions to the Googlers working on it. earlier insertions experimentation results Methodology As with the original post, each experiment involved comparing two or more ways of inserting 1000, 10,000, and 100,000 randomly generated records into two different types of tables: an extremely simple table ( ) consisting of a single integer column, and simple a more real-world table ( ) which describes a collection of music tracks where each contains an id, title, duration, lyrics, etc. The column is this table’s . tracks id PRIMARY KEY I also performed every test on my 16GB Nexus 5X, running API level 25 (Nougat). The results were calculated by tracking the time that had elapsed while all of the inserts for the current size iteration were running. The time taken to connect to the before and to wipe the table after each iteration was excluded from the results. database I’ve updated the from the original post with the new test scenarios using Room. GitHub project Important Notes The version of Room used in these experiments was . 1.0.0-alpha1 Room requires all classes to define a value, and doesn’t let you fall back to SQLite’s default primary key. This means that the Room-based table actually had two columns: an auto-generated id, and the actual value. @Entity @PrimaryKey rowid simple Caveat: Memory Issues The documentation for Room suggests the following approach for batch inserts using the pattern: @Dao @Daopublic interface MyDao {@Insertvoid insertAll(MyItem... items);} This is a nice bit of sugar to make inserting a bunch of items into your database easy, but it does require that you instantiate objects for each item you’re going to insert. When I tried to create 100,000 instances of my entity class, I quickly ran out memory. In order to make my performance tests at least , I had to do some hackery by calling multiple times with smaller collections of objects so there was an opportunity for the garbage collector to tidy up between runs. TrackInfo run insertAll TrackInfo With this caveat in mind, let’s get to the numbers. Experiments After inspecting the code generated by Room, it was clear that the Googlers working on it know about the massive performance gain provided by wrapping multiple inserts/updates/deletes within transactions, so we’ll omit comparing Room against the naive case discussed in my original post. Additionally: in the original post we found out that using directly was slightly faster than using , and since Room’s generated code for insertions uses , let’s just compare apples to apples. SQLiteStatement db.execSQL() SQLiteStatement SQLiteStatement vs DAO insertAll Room seems a little slower than manually using SQLiteStatement for the case. However, that could be chalked up to the fact that the Room experiment’s simple table needed to have a field in addition to the value. simple @PrimaryKey Additionally, it looks like Room is smart and re-uses the SQLiteStatement object for multiple insertions. I found this bit of code in the source in : EntityInsertionAdapter.java _entities Entities to insert*/_public final void insert(T[] entities) {final SupportSQLiteStatement stmt = acquire();try {for (T entity : entities) {bind(stmt, entity);stmt.executeInsert();}} finally {release(stmt);}} /*** Inserts the given entities into the database.** @param That little bit of re-use makes a nice difference during insertions, and my original implementation of SQLiteStatement insertions didn’t do it. This made Room’s insertions of records a bit faster. tracks Finally, I think the caveat above about requiring multiple calls to is what hurt Room on the large 100,000-item case. insertAll Batched SQLiteStatement vs DAO insertAll What happens when you compare a batched-insert SQLiteStatement against Room’s pattern? insertAll Conclusion: Room for Improvement Pun intended. #sorrynotsorry I like Room, I think it has the potential to be a very powerful and useful library. It’ll take a lot of the nasty boilerplate out of using in Android applications, and speed up development greatly. SQLite That said, I think Google still has a bit of work to do if they want to provide good support for situations where developers need to insert a lot of data into their SQLite databases using Room. Here are a few suggestions: Let @Insert DAO Methods Accept a Generator The varargs approach to batch inserts with is nice, but insufficient for large numbers of objects. I would propose allowing annotated methods accept an iterator or iterable which is called before each insertion to provide the next object. Something like the following: @Dao @Insert @Daopublic interface MyDao {@Insertvoid insertAll(Iterator<MyItem> itemIterator);} If you were inserting items from a JSON endpoint on your server, with this approach you could use an with your streaming JSON parser of choice and not have to overload the JVM heap with more objects than necessary. Iterator Implement Batch Insertions Google could also greatly speed up bulk inserts by smartly implementing batch insertions where they create an . Something like this: SQLiteStatement INSERT INTO my_items (...) VALUES (...), (...), ...; It might take a bit of clever code generation to determine how to avoid the situation where you can run into the by SQLite, as well as support earlier versions of SQLite so the app doesn’t crash because you . max number of variables allowed can’t insert more than 500 values at a time I think it’s totally doable though. Allow Using SQLite’s ROWID for Tables When an explicit primary key is not defined for a table, SQLite uses internally. Currently, Room doesn’t allow for you to have a table without an explicit primary key. For most situations this is probably a good thing, but sometimes it can make sense to just use . ROWID ROWID It would be nice if the annotation had a flag you could set to not require specifying a . @Entity @PrimaryKey Other Posts in this Series Insertions EXPLAINing the Virtual Machine

Squeezing Performance from SQLite: Insertions (with Room)

Too Long; Didn't Read

Companies Mentioned

@JasonWyatt

RELATED STORIES