SQLiteDatabase Plugin for MP2 (2 Viewers)

[MJGraf]

Last thought so far was using IsolatoinLevel.ReadUncommitted instead of IsolationLevel.Serializable.
Result is that the database throws an exception. Don't know why, but seems like this doesn't bring us any further. Back to Serializable...

 

[MJGraf]

No, there was one more thought
Running Analyze after the import. This creates a permanent table in the database with technical information, which is supposed to help the query optimizer of SQLite to better optimize the queries. Results as follows:

locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran "ANALYZE;" before
ReadTest 1: 11984ms, 11863ms (23847ms)
ReadTest 2: 193ms, 197ms (390ms)
ReadTest 3: 5.3ms, 5.0ms (10.3ms)
ReadTest 4: 49.7ms, 45.5ms (95.2ms)

This indeed seems to speed up ReadTest 2 and 3 by more than 10% - but ReadTest 4 is more than 10% slower. No idea why.
Repeated the test:

locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran ANALYZE; before
ReadTest 1: 12153ms, 11835ms (23988ms)
ReadTest 2: 210ms, 205ms (405ms)
ReadTest 3: 5.2ms, 9.9ms (15.1ms)
ReadTest 4: 44.9ms, 41.5ms (86.4ms)

Now as you see, ReadTest 2 is slower, albeit not as slow as without analyze, but ReadTest 3 is now even slower than without Analyze.
ReadTest 4 is faster than before, but still slower than the result without Analyze.

So the only conclusion I can make for now is that none of the ideas above really brought a breakthrough. Sometimes it was about 10% faster, but sometimes 10 or 20% slower - all of this more or less driven by coincidence. Seems like we have reached a point at which it is very hard to get additional performance benefits by just tuning some parameters. I will therefore stop doing further optimization tests for now and just wait for system.data.sqlite v 1.0.89.0. This will be the last thing to do before we try to get v0.08 of SQLiteDatabase Plugin ready

 

[MJGraf]

I knew there was one more thing I wanted to try
I read somewhere that using a BLOB as primary key is a very bad idea. MP2 uses Guids as primary keys and I told SQLite to store Guids as BLOBs. New idea therefore, let's store Guids as text (unfortunately Guids are 128 Bit. I also read that you could get a real speed improvement by using INT (64 bit) as primary key, because SQlite creates its own (hidden) RowNumber cloumn for every table unless you use an INT as primary key. In the latter case, the primary key is at the same time used as RowNumber, which saves time. But this is not possible, because we don't get our 128 bit Guid into a 64 bit INT...)

So here the results for Guids stored as string:
locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Store Guids as text (instead of binary)
ImportTest: 20:53 min
ReadTest 1: 12770ms, 12732ms (25502ms)
ReadTest 2: 234ms, 237ms (471ms)
ReadTest 3: 12.7ms, 10.4ms (23.1ms)
ReadTest 4: 51.9ms, 32.9ms (84.8ms)

Result is: writes are slower (expected, because the database file is now a bit bigger and therefore more data to write). ReadTests 2 and 4 are a bit slower, ReadTest 3 is materially slower. So back to storing Guids as binaries (and don't always believe what you read in the internet )

 

[MJGraf]

And for those of you, who can't wait to get the benefits of the upcoming v0.08, there is a little surprise for you
I won't have much time tomorrow, next week and probably next weekend. Unfortunately, system.data.sqlite v1.0.89.0 isn't published, yet. So I thought, it would be a good idea to already start testing with v0.08 Beta 1. When everything goes well, the only thing missing for v0.08 final is the new version of system.data.sqlite. Anything else will stay as it is now for v0.08 final, unless we find a bug of course...
Attached you find the binaries of v0.08 Beta 1. Sources will be pushed to GitHub shortly (@chefkoch: I removed the after build batch file and replaced it by a build.targets file. Additionally I rebased the branch to current dev. I hope this worked... Thanks for your comments at GitHub! The only thing I can't solve is the VS2013 solution file. I only have a license for that version... When we update to VS 2014, I'll buy a license for that as well and then I should be 100% in line again with dev branch...)

v0.08 Beta 1 SHOULD be compatible with v0.07, but PLEASE make a backup of your database file before you try...

This beta should be much faster on imports and a bit faster for read access. It should be noticeably smoother, when using MP2 with multiple clients, in particular if a big import is running in the background. As usual, every test result is welcome.

We will have to test another Beta 2, as soon as I have updated to system.data.sqlite v1.0.89.0. But I thought it is a good idea if we already have test results for my changes to the code. If there is no bug in my changes, new bugs in Beta 2 can only result from the new system.data.sqlite version.

And now let the test results come
Michael

Edit: v0.08 Beta 2 is attached some posts down...

 

[MJGraf]

@morpheus_xx

Sorry, I need a little help with pushing to GitHub. As I wrote, I rebased my local FEAT_SQLiteDatabase branch to dev. When I try to push this to GitHub, I get

"git did not exit cleanly (error code 1)" (using TortoiseGit).

Do I have to make a "force push" of my branch to make this work? Thanks!

 

[breese]

Got the test code... As you know I have no issue starting from scratch.
Anything special you want tested? (Chomp... chomp.. )

 

[morpheus_xx]

@morpheus_xx

Sorry, I need a little help with pushing to GitHub. As I wrote, I rebased my local FEAT_SQLiteDatabase branch to dev. When I try to push this to GitHub, I get

"git did not exit cleanly (error code 1)" (using TortoiseGit).

Do I have to make a "force push" of my branch to make this work? Thanks!

Yes exactly, after you did a rebase, you need to use "force push". And double check that local branch name and remote branch name in push dialog are the same (I only write this, because I already accidently pushed another brach to origin/dev )

 

[breese]

I did a fresh install.
Configured the client to do Movies, Series, and Music in that order (All of my music)
This is a screen shot as Movies was being imported

This as Music started importing


Most notable is the drive access, finally some real access and it seems to be blowing thru everything much faster.
Will upload logs when done....

 

[MJGraf]

Thanks, Morph! It worked - pushed to Github.
And thanks, breese! 6-7 MBytes / sec as disc i/o in average while importing is not that bad taking into account that we read thousands of files and write small peaces of data into the database

 

[morpheus_xx]

New release is there https://www.nuget.org/packages/System.Data.SQLite.x86/1.0.89