Hello everyone,

first of all, many thanks for sqlite.

I have just discovered that we are using a very old version (3.26.0 from end of 2018) of the sqlite amalgamation in our product (a Windows based server product).

I just dropped the latest amalgamation of sqlite (3.39.3) into our product and made performance tests.

While I can successfully create around 24.000 select queries within 15s with version 3.26.0 and maybe even more could be possible (I do not know exactly due to the lack of enough clients firing against our server), with the latest version (3.39.3) already around 17.000 clients take around 30s until their queries are all run. This looks like a serious performance degradation between these versions. All queries are run as select queries, the only thing that is different between my builds is the sqlite amalgamation.

What could possibly be the reason for this?

I am compiling with SQLITE_THREADSAFE=1.

Thanks for any help,

Stefan

There are a gazillion possible reasons.

If you want us to look into this, you should come up with a simple, reproducible test case in which SQLite is slower in version 3.39.3 versus some prior version, and publish that test case here. If we cannot reproduce your problem, then we cannot fix it.

All of the performance tests we run show that 3.39.3 is much faster than 3.26.0. So if you are getting slower performance, your tests must be fundamentally different from ours.

This is exactly what I was planning to do. The only problem being that it takes something like an hour until all my test clients are fully shooting at my server. Maybe I can isolate the problem this way.

Regarding: "...an hour until all my test clients are fully shooting... "

I don't mean to speak for Dr. Hipp, but when he asked for a "simple, reproducible test case" I think he was expecting a set of SQL commands that one can paste into the sqlite commandline interface (directly or via the sqlite fiddle) on a fresh database. Those commands would include one or more SELECTs that you find have slowed significantly with the current version.

Perhps you'll want to use the ".timer on" and ".stats on" commands.

If, though, you believe the queries themselves are no slower, but instead there are greater contention delays in the new version, and manifesting those delays requires your setup, then diagnosing your problem may be much more difficult, and your slow process of bisection may be the first step.

Hello everyone,

first of all: my apologies.

After bisecting and testing numerous versions of the amalgamation, which were all very much faster than 3.26.0, I imagined, that something could have gone wrong with my initial test of version 3.39.3. So I rebuilt with 3.39.3 and tested again. And it indeed is faster than 3.26.0, so please bear with me.

Thanks for any suggestions and again my apologies, I am now pretty confident that replacing 3.26.0 with 3.39.3 is a big win for our product.

Regards,

Stefan Kuhr

As others said, there can be many reasons to that. Let's try some fast checks first:

• Have you analyzed the data after change to Sqlite 3.39?
• Are you comparing warm cache times from 3.26 with cold cache time in 3.39?
• Are you using the same compiler flags?
• Have you timed the most used queries? Perhaps only one query type is slowing everything