I am not sure why are you setting shared cache and exclusive access at the same time, I recommend removing the shared cache altogether as it will add unneeded overhead in your case.

That aside, are you using some form of ORM in your Java application? It could be that object creation in the ORM is dominating the test time.

A couple of issues:

We don't know what the default PRAGMA settings are for your installtion (they can be set using compilation options). So we don't know which settings are different from the ones you've been testing against. You can use PRAGMA commands to find out what the current settings are, and make a table showing what the default settings are vs. what your new settings are.

Setting a huge cache might look like a good idea, but we don't know how your OS is handling a process trying to grab so much memory. A normal strategy for an OS is to just page that cache out to virtual memory, which defeats the point of the cache. You will need to use your OS's utilities to see what it's doing when your program is using a big chunk of the cache you set.

If I remember correctly, Dan mentioned before on this forum that shared cache was introduced for severely memory constrained environments and not recommended for general use. I am sure it makes memory access slower but not sure if that also applies to read only scenarios.

From my own testing, the config that worked best for multiple reader connections was mmap. It always provided the best performance for me. Ymmv as my environment had writers along with the readers.

Also note that a single write will invalidate all the cache that you have accumulated in memory when you are relying on sqlite's page cache, not so with mmap. I am not sure how that works in shared cache mode though, it could be that it is not invalidating, unless of course a change is happening from another none shared cache connection (outside of the process perhaps?)

As for the ORM, I just bring that up because I have been bitten by something similar before, just ensure that whatever you are doing in Java land is not significant enough to influence the outcome of your benchmarking.

If I want to understand what works best on my data, I would try that first in a neutral environment, maybe just using the CLI, the .timer and a script of SQL queries to run. After that I would test in my own specific environment and see if results match.

I don't think so shared_cache is causing problem in my case, because I've also tested by commenting shared_cache.

In my case also I have multiple readers and single write. I wanted to make reads faster so used large cache to achieve this. If you feel mmap_size will help in achieving faster reads is there any thumb rule to come up with mmap_size PRAGMA value given a DB file size?

The default cache size should be -2000 which is about 2 MB. Unless you changed it, of course.

None of those changes will have any appreciable affect on READING the database, except for the negligible increase in cache size and perhaps allowing multiple threads to be used for sorting and restricting temp to memory.

For updates, ignore_check_constraints will only have effect to turn off any check constraints. If you do not want check constraints, simply not coding them into the table definitions might be perspicacious. Otherwise, not much difference.

One reason I thought of is: Since SQLite DB is on SSD time taken to read pages from disk(SSD) and time taken to read pages from memory(cache) is almost same - I may be completely wrong here!

You are incorrect here by about 1000 orders of magnitude.

Reading a "bunch-o-bytes" from RAM is 1000 times (or more) faster (requires only 1 memory read from the application cache or 1 memory read from the system cache plus 1 write to the application cache plus 1 read from the application memory cache) than accessing an SSD to read those same bytes (which would require at least 1 device read, 1 memory write, 1 memory read, and 1 memory write). If it were not then there would be no point in having expensive RAM would there not be?

The best way to optimize I/O is to not do it. It has been thus since the beginning of time.

Agreed!

The mmap_size should match or be a little higher than the size of your active page set. If you are accessing all your records in a completely random fashion then if your memory is large enough you should set the mmap_size to something that can hold the entire database. The mmap size is an upper limit, the system will not allocate that much memory until it is all requested for read/write.