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: 1028081" data-attributes="member: 17886"><p>Thanks Lehmden - will correct that.</p><p></p><p>In the meantime I found out that the result above has apparently noting to do with SQLite. What I did was just wait for one day, use the MP2 Server computer as regular MP1 htpc and run the same Read Test 1 again. The result shows, we are up to the same performance as above with the first test. So apparently this is caused by some kind of Windows caching mechanism...</p><p></p><p><u>Wait one day / Restart MP2 Server / Read Test 1:</u></p><p>SQL1 (Genres): 2ms</p><p>SQL2 (AudioLanguages): 1ms</p><p>SQL3 (Actors): 1ms</p><p>SQL4 (Directors): 1ms</p><p>SQL5 (Writers): 1ms</p><p>SQL6 (Artists): 374ms</p><p>SQL7 (Genres): 23ms</p><p>SQL8 (Albumartist): 64ms</p><p>SQL9 (Composers): 118ms</p><p>SQL10 (Episode): 1ms</p><p>SQL11 (DVDEpisode): 1ms</p><p>SQL12 (Mediaitem): 84ms</p><p></p><p>But at least it's reproducible. This time the cache kicks in already when I run the search for the second time:</p><p></p><p><u>Restart MP2 Server / Read Test 1:</u></p><p>SQL1 (Genres): 1ms</p><p>SQL2 (AudioLanguages): 1ms</p><p>SQL3 (Actors): 1ms</p><p>SQL4 (Directors): 0ms</p><p>SQL5 (Writers): 1ms</p><p>SQL6 (Artists): 82ms</p><p>SQL7 (Genres): 22ms</p><p>SQL8 (Albumartist): 55ms</p><p>SQL9 (Composers): 96ms</p><p>SQL10 (Episode): 1ms</p><p>SQL11 (DVDEpisode): 1ms</p><p>SQL12 (Mediaitem): 56ms</p><p></p><p>And then it stays like that:</p><p></p><p><u>Restart MP2 Server / Read Test 1:</u></p><p>SQL1 (Genres): 1ms</p><p>SQL2 (AudioLanguages): 1ms</p><p>SQL3 (Actors): 0ms</p><p>SQL4 (Directors): 1ms</p><p>SQL5 (Writers): 1ms</p><p>SQL6 (Artists): 83ms</p><p>SQL7 (Genres): 20ms</p><p>SQL8 (Albumartist): 51ms</p><p>SQL9 (Composers): 92ms</p><p>SQL10 (Episode): 1ms</p><p>SQL11 (DVDEpisode): 1ms</p><p>SQL12 (Mediaitem): 55ms</p><p></p><p>We have to keep that in mind for further tests - although I'm not sure, yet, which result we shall take from our tests. While the slower first time results reflect more the raw SQLite performance, the cache also kicks in when using MP2 in real time so these results should be closer to reality. For now I will always perform the test two or three times and record all results to make sure we have them both - with and without the help of windows.</p><p></p><p>But back to the original ideas:</p><p>The first one was whether this has anything to do with the "prepare" command. Since I'm executing exactly the same query multiple times, I thought that maybe SQLite somehow has stored this SQL query in a prepared state so that it can be executed faster when run the second or third time. But this cannot be the case since I restarted the MP2 server between all the tests and there is no way for SQLite to store any kind of prepare statement - unless it is stored in the database file itself.</p><p>While reading about this, I stumbled upon the "<a href="http://sqlite.org/lang_analyze.html" target="_blank">ANALYZE</a>" SQL command for SQLite. What this command does is analyzing all the tables, their contents and the respective indices and storing the results in a permanent internal table of the SQLite database file. These results are then used by the query optimizer of SQLite to improve the performance. Bus as you can read on the linked page, ANALYZE is only run, when you execute it explicitly, which I didn't.</p><p>But hey - maybe we have to run that command after every import!</p><p></p><p>So stay tuned. Tests with ANALYZE will follow...</p><p>Michael</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1028081, member: 17886"] Thanks Lehmden - will correct that. In the meantime I found out that the result above has apparently noting to do with SQLite. What I did was just wait for one day, use the MP2 Server computer as regular MP1 htpc and run the same Read Test 1 again. The result shows, we are up to the same performance as above with the first test. So apparently this is caused by some kind of Windows caching mechanism... [U]Wait one day / Restart MP2 Server / Read Test 1:[/U] SQL1 (Genres): 2ms SQL2 (AudioLanguages): 1ms SQL3 (Actors): 1ms SQL4 (Directors): 1ms SQL5 (Writers): 1ms SQL6 (Artists): 374ms SQL7 (Genres): 23ms SQL8 (Albumartist): 64ms SQL9 (Composers): 118ms SQL10 (Episode): 1ms SQL11 (DVDEpisode): 1ms SQL12 (Mediaitem): 84ms But at least it's reproducible. This time the cache kicks in already when I run the search for the second time: [U]Restart MP2 Server / Read Test 1:[/U] SQL1 (Genres): 1ms SQL2 (AudioLanguages): 1ms SQL3 (Actors): 1ms SQL4 (Directors): 0ms SQL5 (Writers): 1ms SQL6 (Artists): 82ms SQL7 (Genres): 22ms SQL8 (Albumartist): 55ms SQL9 (Composers): 96ms SQL10 (Episode): 1ms SQL11 (DVDEpisode): 1ms SQL12 (Mediaitem): 56ms And then it stays like that: [U]Restart MP2 Server / Read Test 1:[/U] SQL1 (Genres): 1ms SQL2 (AudioLanguages): 1ms SQL3 (Actors): 0ms SQL4 (Directors): 1ms SQL5 (Writers): 1ms SQL6 (Artists): 83ms SQL7 (Genres): 20ms SQL8 (Albumartist): 51ms SQL9 (Composers): 92ms SQL10 (Episode): 1ms SQL11 (DVDEpisode): 1ms SQL12 (Mediaitem): 55ms We have to keep that in mind for further tests - although I'm not sure, yet, which result we shall take from our tests. While the slower first time results reflect more the raw SQLite performance, the cache also kicks in when using MP2 in real time so these results should be closer to reality. For now I will always perform the test two or three times and record all results to make sure we have them both - with and without the help of windows. But back to the original ideas: The first one was whether this has anything to do with the "prepare" command. Since I'm executing exactly the same query multiple times, I thought that maybe SQLite somehow has stored this SQL query in a prepared state so that it can be executed faster when run the second or third time. But this cannot be the case since I restarted the MP2 server between all the tests and there is no way for SQLite to store any kind of prepare statement - unless it is stored in the database file itself. While reading about this, I stumbled upon the "[URL='http://sqlite.org/lang_analyze.html']ANALYZE[/URL]" SQL command for SQLite. What this command does is analyzing all the tables, their contents and the respective indices and storing the results in a permanent internal table of the SQLite database file. These results are then used by the query optimizer of SQLite to improve the performance. Bus as you can read on the linked page, ANALYZE is only run, when you execute it explicitly, which I didn't. But hey - maybe we have to run that command after every import! So stay tuned. Tests with ANALYZE will follow... Michael [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
