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: 1030771" data-attributes="member: 17886"><p>And here is a full set of results - write and read - of the SQLiteDatabase v0.07 with an additional own CachePool, a comparison with a clean v0.07 and my attempt to analyze the results:</p><p><u><strong>SQLiteDatabase (v0.07 with own ConnectionPool (System.Data.SQLite connection pool switched off in connection string)):</strong></u></p><p><u>Import Test</u>: <strong>24:39</strong></p><p><u>Read Test 1</u></p><p>Query Time: 376 ms, 294 ms (<strong>670 ms</strong>)</p><p>Read Time: 12930 ms, 11719 ms (<strong>24649 ms</strong>)</p><p><u>Read Test 2</u></p><p>Query Time: 243 ms, 188 ms (<strong>431 ms</strong>)</p><p>Read Time: 277 ms, 204 ms (<strong>481 ms</strong>)</p><p><u>Read Test 3</u></p><p>Query Time: 2 ms, 0 ms (<strong>2 ms</strong>) [Note: Second test run seems to be below the accuracy of our SQLDebug.Log entries]</p><p>Read Time: <s>15 ms, 3 ms (<strong>18 ms</strong>)</s> 15ms, 8ms (<strong>23ms</strong>) [see next post for reason]</p><p><u>Read Test 4</u></p><p>Query Time: 6 ms, 9 ms (<strong>15 ms</strong>)</p><p>Read Time: 100 ms, 94 ms (<strong>194 ms</strong>)</p><p></p><p><u><strong>Comparison SQLiteDatabase v0.07 vs. SQLiteDatabase (v0.07 with own ConnectionPool):</strong></u></p><p><u>Import Test</u>: <strong>128%</strong></p><p><u>Read Test 1</u></p><p>Query Time: <strong>99%</strong></p><p>Read Time: <strong>100%</strong></p><p><u>Read Test 2</u></p><p>Query Time: <strong>98%</strong></p><p>Read Time: <strong>101%</strong></p><p><u>Read Test 3</u></p><p>Query Time: <strong>150%</strong> [Read with care - may be below the accuracy of the log entries...]</p><p>Read Time: <strong>170%</strong></p><p><u>Read Test 4</u></p><p>Query Time: <strong>227%</strong></p><p>Read Time: <strong>175%</strong></p><p></p><p><u><strong>Analysis:</strong></u></p><p>The <u>ImportTest</u>, i.e. write speed is 28% faster than without the connection pool. This is most likely caused by the time we save in not opening and closing the connection for every transaction.</p><p><u>ReadTest 1</u> is more or less the same speed. I would suppose that the reason is that this search returns too much data to take advantage of the cache (40MB).</p><p><u>ReadTest 2</u> is also more or less the same speed. My guess is that most of the time this search takes is used for the query itself - not for reading the data (see QueryTime vs. ReadTime). The cache mostly helps with the read time - not with the query itself. But we should keep this in mind. The QueryTime here is very long compared to the QueryTime of other searches. It is nearly as long as the QueryTime of ReadTest 1, which in the end returns much more data. Maybe we can improve the QueryTime by analyzing and improving the respective SQL statements.</p><p>The real beauty of the connection pool can be seen in <u>ReadTest 3</u> and <u>ReadTest 4</u>. In particular the ReadTime of the second run of ReadTest 3 improved enormously. I suspect this results from the cache feature really working for the first time. Remember, before, we closed the connection after every transaction, i.e. the cache was destroyed and more or less useless. Now we keep the connection open, i.e. the cache can fulfill its duty. Therefore I guess the second time reading the same data is A LOT faster in ReadTest 3 (8ms vs. 19ms, i.e. more than 2x as fast). It is also faster in ReadTest4, but not as much as in ReadTest 3. The reason may be that the amount of data in ReatTest 4 (33,6MB only for covers) is nearly as big as the cache itself (40MB). A bigger cache may help here - let's see...</p></blockquote><p></p>
[QUOTE="MJGraf, post: 1030771, member: 17886"] And here is a full set of results - write and read - of the SQLiteDatabase v0.07 with an additional own CachePool, a comparison with a clean v0.07 and my attempt to analyze the results: [U][B]SQLiteDatabase (v0.07 with own ConnectionPool (System.Data.SQLite connection pool switched off in connection string)):[/B] Import Test[/U]: [B]24:39[/B] [U]Read Test 1[/U] Query Time: 376 ms, 294 ms ([B]670 ms[/B]) Read Time: 12930 ms, 11719 ms ([B]24649 ms[/B]) [U]Read Test 2[/U] Query Time: 243 ms, 188 ms ([B]431 ms[/B]) Read Time: 277 ms, 204 ms ([B]481 ms[/B]) [U]Read Test 3[/U] Query Time: 2 ms, 0 ms ([B]2 ms[/B]) [Note: Second test run seems to be below the accuracy of our SQLDebug.Log entries] Read Time: [S]15 ms, 3 ms ([B]18 ms[/B])[/S] 15ms, 8ms ([B]23ms[/B]) [see next post for reason] [U]Read Test 4[/U] Query Time: 6 ms, 9 ms ([B]15 ms[/B]) Read Time: 100 ms, 94 ms ([B]194 ms[/B]) [U][B]Comparison SQLiteDatabase v0.07 vs. SQLiteDatabase (v0.07 with own ConnectionPool):[/B] Import Test[/U]: [B]128%[/B] [U]Read Test 1[/U] Query Time: [B]99%[/B] Read Time: [B]100%[/B] [U]Read Test 2[/U] Query Time: [B]98%[/B] Read Time: [B]101%[/B] [U]Read Test 3[/U] Query Time: [B]150%[/B] [Read with care - may be below the accuracy of the log entries...] Read Time: [B]170%[/B] [U]Read Test 4[/U] Query Time: [B]227%[/B] Read Time: [B]175%[/B] [U][B]Analysis:[/B][/U] The [U]ImportTest[/U], i.e. write speed is 28% faster than without the connection pool. This is most likely caused by the time we save in not opening and closing the connection for every transaction. [U]ReadTest 1[/U] is more or less the same speed. I would suppose that the reason is that this search returns too much data to take advantage of the cache (40MB). [U]ReadTest 2[/U] is also more or less the same speed. My guess is that most of the time this search takes is used for the query itself - not for reading the data (see QueryTime vs. ReadTime). The cache mostly helps with the read time - not with the query itself. But we should keep this in mind. The QueryTime here is very long compared to the QueryTime of other searches. It is nearly as long as the QueryTime of ReadTest 1, which in the end returns much more data. Maybe we can improve the QueryTime by analyzing and improving the respective SQL statements. The real beauty of the connection pool can be seen in [U]ReadTest 3[/U] and [U]ReadTest 4[/U]. In particular the ReadTime of the second run of ReadTest 3 improved enormously. I suspect this results from the cache feature really working for the first time. Remember, before, we closed the connection after every transaction, i.e. the cache was destroyed and more or less useless. Now we keep the connection open, i.e. the cache can fulfill its duty. Therefore I guess the second time reading the same data is A LOT faster in ReadTest 3 (8ms vs. 19ms, i.e. more than 2x as fast). It is also faster in ReadTest4, but not as much as in ReadTest 3. The reason may be that the amount of data in ReatTest 4 (33,6MB only for covers) is nearly as big as the cache itself (40MB). A bigger cache may help here - let's see... [/QUOTE]
Insert quotes…
Verification
Post reply
Forums
MediaPortal 2
Plugin Development
SQLiteDatabase Plugin for MP2
Contact us
RSS
Top
Bottom
