Thomas Karam
Jan 17, 2016 · 2 min read

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.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade