Massive performance difference between sqlite amalgamation 3.7 and 3.16

Question

I have a MS Window executable, written in unmanaged plain "C", that places a GUI wrapper around an embedded database engine (SQLite). The SQLite capability is provided by the amalgamation package 3.7.14.1 Specifically the 2 files sqlite3.h and sqlite3.c The only change I make to the "out of the box" files is to add the line: #define SQLITE_ENABLE_COLUMN_METADATA 1

It is all compiled on a Win 7(64bit) system using Visual Studio 2008, BUT I compile it as a 32 bit application.

I have swapped out the files sqlite3.h and sqlite3.c for those from the amalgamation package 3.15.2 Again, the ONLY change is to add the line: #define SQLITE_ENABLE_COLUMN_METADATA 1

I make no changes to my project configuration. I only swap the 2 files!

I have a complex script that loads, to memory, a large DB ~ 600 Mbytes, and then does a lot of SELECTS, and transfers the results to Excel (using the functionality in my GUI wrapper).

My issue. The performance of the version using amalgamation package 3.7.14.1 is over one hundred times faster than the amalgamation package 3.15.2. I am using exactly the same "script".

I tried amalgamation package 3.16 and that is no better. I can see that the performance loss is in the SQL engine (by displaying pop-ups when entering and leaving the SQL machine. In terms of my GUI wrapper, Visual Studio configuration, project configuration, all things are the same. I presume the performance issue lies in some compiler switches in the amalgamation packages.

Does anyone know where I should start to look?

Answer 1

The issue is likely caused by SQLite's Next Generation Query Planner which was introduced in version 3.8.0. Once you find the specific queries that are slow you can try running them prefixed with explain. That will dump the query plan, allowing you to see the difference in plans created by 3.7.14 and 3.15.2.

It's hard to say exactly what the issue is without seeing the query or database, but often query performance can be improved by indexes, so I'd first look for any table scans in the plan that could be avoided by adding indexes. Another thing to try is running analyze on your database to generate statistics tables which help the planner build optimal query plans.