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: 1036211" data-attributes="member: 17886"><p>No, there was one more thought <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin :D" loading="lazy" data-shortname=":D" /></p><p>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:</p><p></p><p><strong>locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran "ANALYZE;" before</strong></p><p>ReadTest 1: 11984ms, 11863ms (<strong>23847ms</strong>)</p><p>ReadTest 2: 193ms, 197ms (<strong>390ms</strong>)</p><p>ReadTest 3: 5.3ms, 5.0ms (<strong>10.3ms</strong>)</p><p>ReadTest 4: 49.7ms, 45.5ms (<strong>95.2ms</strong>)</p><p></p><p>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.</p><p>Repeated the test:</p><p></p><p><strong>locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran ANALYZE; before</strong></p><p>ReadTest 1: 12153ms, 11835ms (<strong>23988ms</strong>)</p><p>ReadTest 2: 210ms, 205ms (<strong>405ms</strong>)</p><p>ReadTest 3: 5.2ms, 9.9ms (<strong>15.1ms</strong>)</p><p>ReadTest 4: 44.9ms, 41.5ms (<strong>86.4ms</strong>)</p><p></p><p>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.</p><p>ReadTest 4 is faster than before, but still slower than the result without Analyze.</p><p></p><p>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 <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite8" alt=":D" title="Big Grin :D" loading="lazy" data-shortname=":D" /></p></blockquote><p></p>
[QUOTE="MJGraf, post: 1036211, member: 17886"] No, there was one more thought :D 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: [B]locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran "ANALYZE;" before[/B] ReadTest 1: 11984ms, 11863ms ([B]23847ms[/B]) ReadTest 2: 193ms, 197ms ([B]390ms[/B]) ReadTest 3: 5.3ms, 5.0ms ([B]10.3ms[/B]) ReadTest 4: 49.7ms, 45.5ms ([B]95.2ms[/B]) 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: [B]locking_mode=Exclusive / Shared Cache / Page Size 4KB / Discarded the respective first result / Ran ANALYZE; before[/B] ReadTest 1: 12153ms, 11835ms ([B]23988ms[/B]) ReadTest 2: 210ms, 205ms ([B]405ms[/B]) ReadTest 3: 5.2ms, 9.9ms ([B]15.1ms[/B]) ReadTest 4: 44.9ms, 41.5ms ([B]86.4ms[/B]) 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 :D [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
