home
products
contribute
download
documentation
forum
Home
Forums
New posts
Search forums
What's new
New posts
All posts
Latest activity
Members
Registered members
Current visitors
Donate
Log in
Register
What's new
Search
Search
Search titles only
By:
New posts
Search forums
Search titles only
By:
Menu
Log in
Register
Navigation
Install the app
Install
More options
Contact us
Close Menu
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser
.
Reply to thread
Message
<blockquote data-quote="MJGraf" data-source="post: 989016" data-attributes="member: 17886"><p>Hi everybody,</p><p>ok, I still have to time to code, but I do it anyway - it's just too much fun <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite7" alt=":p" title="Stick Out Tongue :p" loading="lazy" data-shortname=":p" /></p><p>New version attached.</p><p> </p><p><strong><u>Changes:</u></strong></p><p>I now use SQLiteConnectionStringBuilder instead of concatenating the connection string manually. Makes the code more readyble and is said to keep compatibility if we want to upgrade syste.data.sqlite.dll later while they changed something in the connection string.</p><p>Besides that, I didn't change the SQLiteDatabase class behaviour, I just changed some things in the connection string to improve the behaviour of the SQLite database.</p><p>Most important changes are as follows:</p><p> </p><p><u>BinaryGUID=true</u></p><p>GUIDs are now stored as binaries, not as string. Saves some space in the database and is said to make search queries on GUIDs faster.</p><p> </p><p><u>PageSize=4096</u></p><p>I read that on NTFS this gives some speed improvements because the standard NTFS cluster size is also 4096kb.</p><p>Furthermore, I remember we had some discussion about storing BLOBs (in our case mostly pictures such as covers, etc.) in the database or separately in a file system structure. I found an interesting article here: <a href="http://www.sqlite.org/intern-v-extern-blob.html" target="_blank">http://www.sqlite.org/intern-v-extern-blob.html</a> which also has a speed comparison. According to this article, with a pagesize of 4096kb, it is faster to store BLOBs of up to 50kb in the database (With a pagesize of 8192 this even applies to BLOBs up to 100kb). I assume that most of our BLOBs, such as covers, are of a size up to 50 or 100kb. Maybe we should also try pagesize 8192 to see whether this gives further speed improvements.</p><p> </p><p><u>JournalMode=WAL</u></p><p>WAL means "write ahead log". This is probably the most importand change in this build. You can read more about it here: <a href="http://www.sqlite.org/wal.html" target="_blank">http://www.sqlite.org/wal.html</a></p><p>The improvement with this mode is mainly that write locks do not block reads anymore. As a result, there are much fewer situations, in which one connection to the database is blocked by another connection. This means that we have to rely less often on the timeout of 30 sec. mentioned above. Anyway, I didn't have the situation anymore, which I mentioned above and in which I got timeout exceptions when adding a share while a big import was running. The whole system seemed to be even more fluid.</p><p>The only disadvantage I can see is that we may have three database files instead of one (1. the database itself, 2. the journal log file and 3. an index file for the journal log).</p><p> </p><p>The overall result is that these new settings are already an improvement for me. I did the same import as above (24k music files) which now only took 36 minutes instead of 42 minutes and the database file was about 50MB smaller (ok the latter one is not that big of an improvement...).</p><p> </p><p>What I still would like to try is to use just a single connection to the database, which is then opened on MP2-Server start and remains open until the server is shut down. All the transactions later on are then based on this single connection. I assume that opening and closing the connection again and again takes a lot of time and we may see further speed improvements by this.</p><p> </p><p>Tests are still welcome <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /></p><p>Michael</p><p> </p><p>[Attachment removed - please use version from first post]</p></blockquote><p></p>
[QUOTE="MJGraf, post: 989016, member: 17886"] Hi everybody, ok, I still have to time to code, but I do it anyway - it's just too much fun :p New version attached. [B][U]Changes:[/U][/B] I now use SQLiteConnectionStringBuilder instead of concatenating the connection string manually. Makes the code more readyble and is said to keep compatibility if we want to upgrade syste.data.sqlite.dll later while they changed something in the connection string. Besides that, I didn't change the SQLiteDatabase class behaviour, I just changed some things in the connection string to improve the behaviour of the SQLite database. Most important changes are as follows: [U]BinaryGUID=true[/U] GUIDs are now stored as binaries, not as string. Saves some space in the database and is said to make search queries on GUIDs faster. [U]PageSize=4096[/U] I read that on NTFS this gives some speed improvements because the standard NTFS cluster size is also 4096kb. Furthermore, I remember we had some discussion about storing BLOBs (in our case mostly pictures such as covers, etc.) in the database or separately in a file system structure. I found an interesting article here: [url]http://www.sqlite.org/intern-v-extern-blob.html[/url] which also has a speed comparison. According to this article, with a pagesize of 4096kb, it is faster to store BLOBs of up to 50kb in the database (With a pagesize of 8192 this even applies to BLOBs up to 100kb). I assume that most of our BLOBs, such as covers, are of a size up to 50 or 100kb. Maybe we should also try pagesize 8192 to see whether this gives further speed improvements. [U]JournalMode=WAL[/U] WAL means "write ahead log". This is probably the most importand change in this build. You can read more about it here: [url]http://www.sqlite.org/wal.html[/url] The improvement with this mode is mainly that write locks do not block reads anymore. As a result, there are much fewer situations, in which one connection to the database is blocked by another connection. This means that we have to rely less often on the timeout of 30 sec. mentioned above. Anyway, I didn't have the situation anymore, which I mentioned above and in which I got timeout exceptions when adding a share while a big import was running. The whole system seemed to be even more fluid. The only disadvantage I can see is that we may have three database files instead of one (1. the database itself, 2. the journal log file and 3. an index file for the journal log). The overall result is that these new settings are already an improvement for me. I did the same import as above (24k music files) which now only took 36 minutes instead of 42 minutes and the database file was about 50MB smaller (ok the latter one is not that big of an improvement...). What I still would like to try is to use just a single connection to the database, which is then opened on MP2-Server start and remains open until the server is shut down. All the transactions later on are then based on this single connection. I assume that opening and closing the connection again and again takes a lot of time and we may see further speed improvements by this. Tests are still welcome ;) Michael [Attachment removed - please use version from first post] [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom