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: 1028442" data-attributes="member: 17886"><p>Finally one more Read Test 4 - but this time in "hardcore". I did everything as described above, but (a) I used "large list" to make MP2 Client more responsive without affecting the database results and (b) I did not enter the first album displayed, but I entered "Innuendo" by Queen. The reason why I chose this album is that the embedded cover art in these tracks is huge. In each flac file (12 in total) there is a cover file with <u><strong>2,8MB</strong></u> embedded (yes, I like Queen which is the reason why these covers are REALLY high res <img src="data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7" class="smilie smilie--sprite smilie--sprite2" alt=";)" title="Wink ;)" loading="lazy" data-shortname=";)" /> ). Results are as follows:</p><p></p><p>SQL1 (ProviderResource): 3ms</p><p>SQL2 (Artists): 1ms</p><p>SQL3 (Genres): 1ms</p><p>SQL4 (Albumartists): 0ms</p><p>SQL5 (Composers): 2ms</p><p>SQL6 (ProviderResource): 14ms</p><p></p><p>So what I conclude is the following:</p><ul> <li data-xf-list-type="ul">SQL6 (the one including the covers) takes 7x as long as in the last test when entering an album with 11 tracks each having a cover of 49KB - but 14ms in this context are still negligible.</li> <li data-xf-list-type="ul">On the frontend, entering the album was noticeable slower than in the last test (didn't measure it, but maybe 4 or 5 seconds instead of less than 1sec)</li> <li data-xf-list-type="ul">It seems nevertheless that the database is no bottleneck here - all the SQL queries in total take only 21ms.</li> <li data-xf-list-type="ul">However, I'm starting to wonder whether the time to execute the queries which is shown in SQLDebug.log are really the right figures to measure our database performance - in particular with respect to BLOBS. The reason for my doubts is as follows:<ul> <li data-xf-list-type="ul">we are loading 12 tracks with a cover of 2,8MB each. So we should load 12 x 2,8MB = 33,6MB in total.</li> <li data-xf-list-type="ul">According to SQLDebug.log this took 14ms.</li> <li data-xf-list-type="ul">If this was really the case, we had a pure transfer speed of <u><strong>2,4GB/sec</strong></u> - which simply cannot be true....</li> </ul></li> </ul><p>I have two possible explanations for this:</p><ul> <li data-xf-list-type="ul">I don't know whether we somehow resize the covers while we import them, so that the covers in the database are actually much smaller than the original ones in the flac files. @[USER=48495]morpheus_xx[/USER]: Can you shed some light on this? Thanks!</li> <li data-xf-list-type="ul">The second possibility would be that executing the SQL query somehow "prepares" the results (which takes 14ms) but it doesn't really transfer them into memory, yet. Reading the covers from disk would then happen afterward when we actually put together our MIA objects using the SQLiteDataReader. This would really be a problem regarding performance tests because in this case we would have to put debug code in form of System.Diagnostic.Stopwatch directly into the code and write the results to the Server.log file. So let's hope it's the first one...</li> </ul><p>If the first one is true, I would come to the conclusion that putting BLOBS into our database is not a problem at all, because the results above are really reasonable and I doubt whether it would be much faster when we store the pictures in the file system.</p><p></p><p>Michael</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1028442, member: 17886"] Finally one more Read Test 4 - but this time in "hardcore". I did everything as described above, but (a) I used "large list" to make MP2 Client more responsive without affecting the database results and (b) I did not enter the first album displayed, but I entered "Innuendo" by Queen. The reason why I chose this album is that the embedded cover art in these tracks is huge. In each flac file (12 in total) there is a cover file with [U][B]2,8MB[/B][/U] embedded (yes, I like Queen which is the reason why these covers are REALLY high res ;) ). Results are as follows: SQL1 (ProviderResource): 3ms SQL2 (Artists): 1ms SQL3 (Genres): 1ms SQL4 (Albumartists): 0ms SQL5 (Composers): 2ms SQL6 (ProviderResource): 14ms So what I conclude is the following: [LIST] [*]SQL6 (the one including the covers) takes 7x as long as in the last test when entering an album with 11 tracks each having a cover of 49KB - but 14ms in this context are still negligible. [*]On the frontend, entering the album was noticeable slower than in the last test (didn't measure it, but maybe 4 or 5 seconds instead of less than 1sec) [*]It seems nevertheless that the database is no bottleneck here - all the SQL queries in total take only 21ms. [*]However, I'm starting to wonder whether the time to execute the queries which is shown in SQLDebug.log are really the right figures to measure our database performance - in particular with respect to BLOBS. The reason for my doubts is as follows: [LIST] [*]we are loading 12 tracks with a cover of 2,8MB each. So we should load 12 x 2,8MB = 33,6MB in total. [*]According to SQLDebug.log this took 14ms. [*]If this was really the case, we had a pure transfer speed of [U][B]2,4GB/sec[/B][/U] - which simply cannot be true.... [/LIST] [/LIST] I have two possible explanations for this: [LIST] [*]I don't know whether we somehow resize the covers while we import them, so that the covers in the database are actually much smaller than the original ones in the flac files. @[USER=48495]morpheus_xx[/USER]: Can you shed some light on this? Thanks! [*]The second possibility would be that executing the SQL query somehow "prepares" the results (which takes 14ms) but it doesn't really transfer them into memory, yet. Reading the covers from disk would then happen afterward when we actually put together our MIA objects using the SQLiteDataReader. This would really be a problem regarding performance tests because in this case we would have to put debug code in form of System.Diagnostic.Stopwatch directly into the code and write the results to the Server.log file. So let's hope it's the first one... [/LIST] If the first one is true, I would come to the conclusion that putting BLOBS into our database is not a problem at all, because the results above are really reasonable and I doubt whether it would be much faster when we store the pictures in the file system. Michael [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
