Reply to thread

Message: <blockquote data-quote="MJGraf" data-source="post: 1031590" data-attributes="member: 17886">Although it's only early in the afternoon, I think I need some beer now...I did a lot of tests in the meantime and I learned a lot. I also found a bug in my code, which was responsible for a performance improvement. I'll explain later how this is possible. What I found out a couple of minutes ago, however, means that more or less all my tests I did since <a href="https://forum.team-mediaportal.com/threads/sqlitedatabase-plugin-for-mp2.118535/page-12#post-1030750" target="_blank">this post</a>, are useless. Hence, the beer...As I explained there, I started to use an URI instead of a regular file path and name to be able to enable the Shared Cache mode of SQLite. What I didn't know is that when you use an URI, SQLite ignores your page size setting. I just tried this several times and checked with an external database tool the page sizes used in the database files. When using an URI, the page size is always 1024 bytes - no matter what page size value you specify in the connection string. When using a regular file path and name instead, the page size in the connection string is actually applied. I don't know whether this is a bug in System.Data.SQLite or whether this is intended behavior because it's not possible to have a page size different from 1024 bytes when using Shared Cache mode. The documentation of SQLite doesn't say anything about it. But the really nasty thing is now that many of the other parameters are measured in number of pages - e.g. the cache size. So when I said use a cache size of 10000 pages and I thought the cache size was 10.000 x 4KB due to a page size of 4KB resulting in a cache of about 40MB it actually was only 10M because the page size was 1KB instead of 4KB. The same applies to the "PRAGMA wal_autocheckpoint" statement. There you can specify after what number of pages a checkpoint is run automatically. Of course I assumed wrong values in Megabytes there as well.This is really annoying because (a) all my tests are at least partly useless and (b) we cannot use Shared Cache mode if we want to have a page size different from 4KB. For now I will drop the Shared Cache mode and do some further tests with the page size. Let's see what the outcome is...But there is also good news: The last ImportTest took 20:59 minutes. We are getting there <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink    ;)" loading="lazy" data-shortname=";)" /></blockquote>

Verification