To Project Or Not Project
While writing a ContentProvider for an Android app I wondered, when I call query() on a ContentProvider, should I start specifying a projection? One of the parameters to ContentProvider.query() is an array of Strings, called the projection. Each element in the array should be the name of a column you would like to have returned by the query.
Now most times my own use of a SQLite database in an Android app is pretty basic. I want to persist an object that is more complex than what SharedPreferences would allow, or I need to store multiple objects. I’m really limiting the use of the database to CRUD operations. So generally when I call ContentProvider.query() the projection is null, meaning all of the columns in the database are returned. That’s what I want, but maybe there was a performance hit I was taking by not specifying a projection? And then I thought, well what if I only want a couple of the columns? I should provide a projection in that case, right?
Turns out no to the first and yes to the second. I ran queries on database tables with a column counts ranging from 5 to 16, with the number of rows in the table ranging from 100 to 5000. Compared to calling query() with a null projection, passing a projection that passes all of the columns of the table actually performs worse! On average specifying all of the table columns resulted in a query that took 16% longer. But when you want select columns, specifying those columns always results in a faster query than passing null as the projection. As the number of columns in the projection increases so does the query time.
The take away, specify your projection if you want less than all of the columns from the table you are querying. And if you want all columns pass null as the projection.