Reply to thread

Message

<blockquote data-quote="MJGraf" data-source="post: 1028428" data-attributes="member: 17886">Read Test 3For the next test I continued from the Connection Test and just entered the audio section of my MP2 Client. As a result, there are 2.306 albums displayed and it took about 29 seconds until they showed up. This is extremely long, but to cut a long story short, it is probably caused by (1) my MP2 Client is connected via a slow WIFI connection and (2) we do not have a VirtualStackPanel, yet. But back to the database: Entering the audio section resulted in 2 SQL queries:SQL1 (ProviderResouce):[CODE]SELECT&nbsp; COUNT(&nbsp; V.C) C,&nbsp; V.A0FROM (&nbsp; SELECT&nbsp; DISTINCT T1.MEDIA_ITEM_ID C,&nbsp; T0.ALBUM A0FROM M_PROVIDERRESOURCE T1&nbsp; INNER JOIN M_MEDIAITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID&nbsp; WHERE&nbsp; (&nbsp; T1.SYSTEM_ID = @V0&nbsp; OR T1.SYSTEM_ID = @V1)) VGROUP BY&nbsp; V.A0-------------------------------------------------------&quot;V0&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V1&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]SQL2 (ProviderResource):[CODE]SELECT&nbsp; COUNT(&nbsp; V.C) C,&nbsp; V.A0FROM (&nbsp; SELECT&nbsp; DISTINCT T1.MEDIA_ITEM_ID C,&nbsp; T0.ALBUM A0FROM M_PROVIDERRESOURCE T1&nbsp; INNER JOIN M_MEDIAITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID&nbsp; WHERE&nbsp; (&nbsp; T1.SYSTEM_ID = @V0&nbsp; OR T1.SYSTEM_ID = @V1)) VGROUP BY&nbsp; V.A0-------------------------------------------------------&quot;V0&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V1&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]Test result:SQL1 (ProviderResource) : 697msSQL2 (ProviderResource): 209msThe first observation is that only 0.9s of the 29sec until the albums are shown are caused by the database. The performance problem is therefore definitely NOT the database.But it seems that we have nevertheless found a bug! When you have a close look at the two SQL queries, you will notice that they are identical. Somehow, the MP2 System runs the same query twice - which should not be the case. So there is room for improvement, but (1) this improvement will not lead to the MP2 System being much faster (only 0.2sec from a total of 29sec) and (2) this improvement does not have anything to do with the particular database - it is a bug in the core system. And what you can also see is that SQLites caching mechanism works quite well. The second query is 3.5 times as fast as the first identical one...Last but not least: I carried out this test with the MP2 standard skin - as a result, the albums are shown without covers. I don't know whether this has changed with the Titanium skin - will try later...</blockquote>

[QUOTE="MJGraf, post: 1028428, member: 17886"] [U][B]Read Test 3[/B][/U] For the next test I continued from the Connection Test and just entered the audio section of my MP2 Client. As a result, there are 2.306 albums displayed and it took about [U][B]29 seconds[/B][/U] until they showed up. This is extremely long, but to cut a long story short, it is probably caused by (1) my MP2 Client is connected via a slow WIFI connection and (2) we do not have a VirtualStackPanel, yet. But back to the database: Entering the audio section resulted in 2 SQL queries: [U]SQL1 (ProviderResouce):[/U] [CODE] SELECT COUNT( V.C ) C, V.A0 FROM ( SELECT DISTINCT T1.MEDIA_ITEM_ID C, T0.ALBUM A0 FROM M_PROVIDERRESOURCE T1 INNER JOIN M_MEDIAITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID WHERE ( T1.SYSTEM_ID = @V0 OR T1.SYSTEM_ID = @V1 ) ) V GROUP BY V.A0 ------------------------------------------------------- "V0" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V1" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]SQL2 (ProviderResource):[/U] [CODE] SELECT COUNT( V.C ) C, V.A0 FROM ( SELECT DISTINCT T1.MEDIA_ITEM_ID C, T0.ALBUM A0 FROM M_PROVIDERRESOURCE T1 INNER JOIN M_MEDIAITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID WHERE ( T1.SYSTEM_ID = @V0 OR T1.SYSTEM_ID = @V1 ) ) V GROUP BY V.A0 ------------------------------------------------------- "V0" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V1" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]Test result:[/U] SQL1 (ProviderResource) : 697ms SQL2 (ProviderResource): 209ms The first observation is that only 0.9s of the 29sec until the albums are shown are caused by the database. The performance problem is therefore definitely NOT the database. But it seems that we have nevertheless found a bug! When you have a close look at the two SQL queries, you will notice that they are identical. Somehow, the MP2 System runs the same query twice - which should not be the case. So there is room for improvement, but (1) this improvement will not lead to the MP2 System being much faster (only 0.2sec from a total of 29sec) and (2) this improvement does not have anything to do with the particular database - it is a bug in the core system. And what you can also see is that SQLites caching mechanism works quite well. The second query is 3.5 times as fast as the first identical one... Last but not least: I carried out this test with the MP2 standard skin - as a result, the albums are shown without covers. I don't know whether this has changed with the Titanium skin - will try later... [/QUOTE]

Verification