Squeezing Performance from SQLite: EXPLAINing the Virtual Machine | Hacker News Reader

Squeezing Performance from SQLite: EXPLAINing the Virtual Machine

medium.com

109 pointsthunderbong9 comments

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.

[0] https://medium.com/@JasonWyatt/squeezing-performance-from-sq...

[1] https://medium.com/@JasonWyatt/squeezing-performance-from-sq...

I find EXPLAIN QUERY PLAN a lot more useful to figuring out perf issues with sqlite. You can immediately see the table scan and tweak the query until it goes away. It still doesn't EXPLAIN everything, like

- why did it choose not to use an index?

- why does it need to do a scan of another table (oh there is FK)

- which part of the query plan is actually the expensive one.

For that you need to go to something like this:

https://github.com/asutherland/grok-sqlite-explain

http://www.visophyte.org/blog/2010/04/06/performance-annotat...

No one's commented yet, but wow, I had no idea. This is a fascinating read, and very accessible.

Why was there a NOOP in the table creation byte code? And do other database engines take this approach?

> And do other database engines take this approach?

It's not exactly the same, but Postgres includes an LLVM-based JIT that compiles queries for reuse[1].

[1]: https://www.postgresql.org/docs/current/jit-reason.html

>Why was there a NOOP in the table creation byte code?

I don't see the NOOP in the article, but you do see these in sqlite when they, for example, have a struct of opcodes do dual duty for something like open-for-reading and open-for writing, where the struct has this:

    /* One of the following two instructions is replaced by an OP_Noop. */
    {OP_OpenRead, 0, 0, 0},        /* 3: Open cursor 0 for reading */
    {OP_OpenWrite, 0, 0, 0},       /* 4: Open cursor 0 for read/write */

> 13. Close 0 0 0 — Close the cursor we opened previously (cursor 0). > > 14. Close 0 0 0 — No-op, since the cursor is already closed

I guess that depends on what exactly you mean by "this approach"? In case you mean running queries in a VM, Firebird/Interbase has had it since the 1980s (in form of the Binary Language Representation, or BLR).

Not sure it'll make any difference to the VM sqlite uses, but when compiling for real processors NOOPs are sometimes added where word alignment or lack thereof can have performance impacts on subsequent instructions, either when running them or for instructions elsewhere that jump to them.

Reply on news.ycombinator.com