Where did you download the driver from?

Have you raised an issue on the Github issues page?

https://github.com/xerial/sqlite-jdbc/issues

While the driver uses the SQLite library, the driver is not maintained by the SQLite developers - they're separate projects.

Hi Chris,

Thanks for your quick response .

We are using it as maven dependency https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc though we have tested it with jar from maven as well from https://jar-download.com/artifact-search/sqlite-jdbc , so having same problem with both sources.

So you mean , i need to raise new issue to https://github.com/xerial/sqlite-jdbc/issues ?

So you mean , i need to raise new issue to https://github.com/xerial/sqlite-jdbc/issues ?

Yes. If you can demonstrate regressions using the sqlite C library or CLI app, this is the proper forum, otherwise such reports belong with the maintainers of the corresponding wrapper.

Hi Mukesh.

...after upgrading sqlite-jdbc version from 3.39.4.1 to 3.40.0.0 and...

Do you know if the sqlite-jdbc driver version is based on the SQLite versions? In other words, does the sqlite-jdbc 3.39.4.1 was based on SQLite v3.39.4? If so, there were a few issues with SQLite v3.40.0 and they were JOIN related. The only way I know is because I had a problem with that version. My suggestion is to take a look at this post and start from there. The sqlite-jdbc driver may gain some speed back by updating to SQLite v3.42.0.

Hi Jose,

Thanks for your input.

The sqlite-jdbc driver may gain some speed back by updating to SQLite v3.42.0.

-> I tried the version 3.42.0.0 as the core problem was for us to fix the vulnerability https://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2023-32697 hence upgraded sqlite-jdbc version from 3.39.4.1 -> 3.42.0.0 but at the same time we tested all the versions > 3.39.4.1 and found that queries are hanging infitely.

I suggest to take out the sqlite-jdbc driver out of the equation: download the CLI tool; connect to the DB and try the query on the CLI. If you can replicate the same issues, response and hanging with the CLI tool, then you can post the query here and the forum and the developers can provide support. Ihth.

Regarding the query itself, it would be treated as confidential information and cannot be provided...

Queries which demonstrate performance regressions are always treated with priority, but without a way to reproduce the regression, there's little which can be done about it. If you can demonstrate an equivalently-failing query, perhaps with randomized or anonymized data and an equivalent schema, you can be sure that it will be looked into promptly.

Alternately, professional support is available, which provides confidentiality of any database you share with the sqlite developers for evaluating such problems.

Hi Mukesh.

However when we run the same select with sqlite 3.42.0, the select was running for a minute at least before I cancelled it, and it had only generated 1274 records in the txt file by then.

Well, you have found something that appears to be not right with versions 3.40.x, 3.41.x and 3.42.0.

One more suggestion: If you know how to do it, I would do one more thing: try to build the CLI with the trunk and test against it. This is not released as v3.43.0 yet, but it's being worked. If that also displays the same slowness, then you would need to provide a sample script to the development so they can see where the problem is. Or, you can work with them to provide the DB and the set of commands that cause the unwanted situation. I can tell you that my problem was fixed with 3.42.0, and I am now using the pre-release 3.43.0 which has also fixed other slowness that I did not bother to report. I hope this guides you some. I wish wisdom in your decision.

Hi Jose, I'm working with Mukesh and I was able to compile the latest against the zip from the instructions here: https://sqlite.org/src/dir?ci=trunk.

Release 3.43 with the commits up to 30th June still show the same delays for us. Sorry about the delayed response in testing this but for now we will look into randomizing data as you and Stephan Beal said in the other reply.

I'll get back to you once we have data that everyone can test with to see the differences.

I've uploaded the db and query as a zip to the following link that you should be able to access (I couldnt see where to attach it here): https://ibm.box.com/s/xrd9v479m2omdmxmxnhe90gdp6qz9lj0

I have tested the anonymized data on commandline. The oldest version I had was SQLite version 3.38.2 2022-03-26 13:51:10. It took about 35 seconds to run and generated a file over 1 GB due to the way the data was converted. With the real data it returned less and a bit faster, but for the purpose of this investigation it still finished relatively fast.

The latest version I tested was SQLite version 3.42.0 2023-05-16 12:36:15, although 3.40 release as I mentioned before should see the same delays. The same query on the same db linked, ran for nearly 8 mins before I killed the job. By that point it had only generated a 70.5MB output file (with the same content so far).

Let us know if there is any issue reproducing the issue with this content. Otherwise I look forward to hearing about any issues you can find.

Hi, just bumping this up to get an update if anyone has had a chance to review...

Hi, just bumping this up to get an update if anyone has had a chance to review...

This probably fell through the cracks because of the reference to JDBC in the subject (which is not maintained here). The link you posted is currently stale. Can you please post a new link?

Edit: nevermind! Dan had a copy locally.

Thanks! Just in case here is a fresh link that has an extended time limit: https://ibm.box.com/s/ngqjpkjk3ixbcajz6hjr74g46d69qsgc

Just in case here is a fresh link that has an extended time limit:

Please try out src:76152ad2ffe56034 or later (the current trunk) and let us know if that resolves the problem for you.

Thanks Stephan, this is looking good!

I pulled the very latest - commit 80927c3913561dddf75cf73be871d93ae06b16f83e8cc36fc360765014209615 from yesterday and tested with that. I got the correct result in approx 18 seconds. I think the previous working test on 3.38 version was about 26 seconds so its a bit quicker now too.

We had already created an extra temp table to get by on 3.42 but we will keep an eye out for new jars that will include this fix for the extra performance improvements. Hopefully these changes can make it to 3.43 official release.

Thanks again, we can close this I think.

Just as a heads-up, through a long bisect process, I can now confirm that I'm running into what seems to be another problem caused by this change: https://www.sqlite.org/cgi/src/info/609fbb94b8f01d6792e5941ab23ce041313d359f6788c4dde6b1ca749ab49137 - which points back to this thread.

Sadly, unlike the poster above, this regression still exists for me in 3.43 as well as the latest trunk as of a few minutes ago. A single query regresses from ~700ms to ~3000ms.

I'll do my best to provide more info in a separate report.