All in all a good writeup and I enjoyed his series so far. In his inserts article [0], I was going to nitpick about the change in records/s for the "same" code, but then I noticed that he actually changed the code subtly (ContentValues changed to Object) and assumed it was probably that. Also he acknowledges this difference at the bottom of the article:
> P.P.S. If you were paying attention, you might have noticed some funkiness going on with my numbers in the charts as well as the records per second breakdowns. They fluctuate kind of widely between tests. For example: the time it took to insert 100,000 track records within a transaction using db.insert() in the first two experiments went from 91.552 seconds all the way up to 145.607 seconds.
I do wish he would have also used explain to analyse what these inserts were doing really for further insight into SQLite. To be honest, I am left wondering how much of what is being observed is specific to the Java/Android/SQLite environment and how much is actually core SQLite behavior.
Also, another nit specific to the article submitted [1]... The title is a bit inaccurate imo. The article demonstrates how to understand the EXPLAIN output, but it doesn't really explain (no pun intended) how to actually implement optimisations using this information. To me that isn't obvious. Would we have to change the SQLite source to implement a better query plan? Is there a configuration? How do we make use of this explain output to come up with a better result? Without that, I think the article lacks the "Squeezing Performance" part. I read the other articles in the series as well, and none of them use explain information to make optimisations either.
> P.P.S. If you were paying attention, you might have noticed some funkiness going on with my numbers in the charts as well as the records per second breakdowns. They fluctuate kind of widely between tests. For example: the time it took to insert 100,000 track records within a transaction using db.insert() in the first two experiments went from 91.552 seconds all the way up to 145.607 seconds.
I do wish he would have also used explain to analyse what these inserts were doing really for further insight into SQLite. To be honest, I am left wondering how much of what is being observed is specific to the Java/Android/SQLite environment and how much is actually core SQLite behavior.
Also, another nit specific to the article submitted [1]... The title is a bit inaccurate imo. The article demonstrates how to understand the EXPLAIN output, but it doesn't really explain (no pun intended) how to actually implement optimisations using this information. To me that isn't obvious. Would we have to change the SQLite source to implement a better query plan? Is there a configuration? How do we make use of this explain output to come up with a better result? Without that, I think the article lacks the "Squeezing Performance" part. I read the other articles in the series as well, and none of them use explain information to make optimisations either.
[0] https://medium.com/@JasonWyatt/squeezing-performance-from-sq...
[1] https://medium.com/@JasonWyatt/squeezing-performance-from-sq...