0

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?

chiffi
  • 9
  • 3
  • Do you have any indexes? Can you find out which query is responsible? – CL. Dec 05 '16 at 20:44
  • 1
    Show the database schema. – CL. Dec 06 '16 at 16:57
  • I have found copies of the amalgamation packages at the Oregon State University Open Source Library. I have now repeated the test with 13 packages (3.16 back to 3.08.01). All shown the same lack of "out of the box" performance with my test. I found that 3.7.17 was by far the best in handling my tests. Now I will think about comparing the 3.7.17 with 3.8.01 at the source code level. But this will probably be beyond me unless it turns out to be a complier switch. – chiffi Dec 06 '16 at 16:59
  • The DB consists of some 90+ tables. None are indexed. What is strange, at least to me, is that the performance is SO different between 3.7.14 or .17 and the current 3.16 versions. In each case I am using the sqlite3.c/h files "out of the box", except for my one #define. – chiffi Dec 06 '16 at 17:04
  • A typical query is too big for this comment field, but the query's, only SELECTs, make significant use of LIKE with wild cards, and have 52 lines of the form , sum (CASE WHEN ( "DTENDE KW" LIKE '%KW02%' ) THEN 1 ELSE 0 END) as KW02 A typical query is > 5000 characters. – chiffi Dec 06 '16 at 17:25
  • To edit the question, click "edit". And such LIKEs cannot be optimized anyway, there is something else that is different. Identify (at least) one query that runs slower. – CL. Dec 06 '16 at 18:09

1 Answers1

0

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.

Connor Pearson
  • 63,902
  • 28
  • 145
  • 142