Reply to thread

Message

<blockquote data-quote="MJGraf" data-source="post: 1028432" data-attributes="member: 17886">Read Test 4:Now I enter the first album shown on the screen. This works quite fast - I didn't measure the time, but it was maybe 1sec or so. As a result, I can see 11 tracks of that album - this time WITH covers. Entering this album results in 6 SQL queries:SQL1 (ProviderResource):[CODE]SELECT&nbsp; COUNT(&nbsp; V.C) C,&nbsp; V.A0FROM (&nbsp; SELECT&nbsp; DISTINCT T1.MEDIA_ITEM_ID C,&nbsp; T0.TITLE A0FROM M_PROVIDERRESOURCE T1&nbsp; INNER JOIN M_MEDIAITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID WHERE&nbsp; (&nbsp; T2.ALBUM = @V0&nbsp; AND (&nbsp; T1.SYSTEM_ID = @V1&nbsp; OR T1.SYSTEM_ID = @V2))) VGROUP BY&nbsp; V.A0-------------------------------------------------------&quot;V0&quot; [String]: '#1'&quot;V1&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V2&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]SQL2 (Artists):[CODE]SELECT&nbsp; T0.MEDIA_ITEM_ID A0,&nbsp; T1.ATTRIBUTE_VALUE A1FROM NM_ARTISTS T0&nbsp; INNER JOIN V_ARTISTS T1 ON T0.ID = T1.ID&nbsp; INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_IDWHERE&nbsp; (&nbsp; T2.ALBUM = @V0&nbsp; AND (&nbsp; T3.SYSTEM_ID = @V1&nbsp; OR T3.SYSTEM_ID = @V2))-------------------------------------------------------&quot;V0&quot; [String]: '#1'&quot;V1&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V2&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]SQL3 (Genres):[CODE]SELECT&nbsp; T0.MEDIA_ITEM_ID A0,&nbsp; T1.ATTRIBUTE_VALUE A1FROM NM_GENRES_0 T0&nbsp; INNER JOIN V_GENRES_0 T1 ON T0.ID = T1.ID&nbsp; INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_IDWHERE&nbsp; (&nbsp; T2.ALBUM = @V0&nbsp; AND (&nbsp; T3.SYSTEM_ID = @V1&nbsp; OR T3.SYSTEM_ID = @V2))-------------------------------------------------------&quot;V0&quot; [String]: '#1'&quot;V1&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V2&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]SQL4 (Albumartists):[CODE]SELECT&nbsp; T0.MEDIA_ITEM_ID A0,&nbsp; T1.ATTRIBUTE_VALUE A1FROM NM_ALBUMARTISTS T0&nbsp; INNER JOIN V_ALBUMARTISTS T1 ON T0.ID = T1.ID&nbsp; INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_IDWHERE&nbsp; (&nbsp; T2.ALBUM = @V0&nbsp; AND (&nbsp; T3.SYSTEM_ID = @V1&nbsp; OR T3.SYSTEM_ID = @V2))-------------------------------------------------------&quot;V0&quot; [String]: '#1'&quot;V1&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V2&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]SQL5 (Composers):[CODE]SELECT&nbsp; T0.MEDIA_ITEM_ID A0,&nbsp; T1.ATTRIBUTE_VALUE A1FROM NM_COMPOSERS T0&nbsp; INNER JOIN V_COMPOSERS T1 ON T0.ID = T1.ID&nbsp; INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_IDWHERE&nbsp; (&nbsp; T2.ALBUM = @V0&nbsp; AND (&nbsp; T3.SYSTEM_ID = @V1&nbsp; OR T3.SYSTEM_ID = @V2))-------------------------------------------------------&quot;V0&quot; [String]: '#1'&quot;V1&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V2&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]SQL6 (ProviderResource):[CODE]SELECT&nbsp; T0.MEDIA_ITEM_ID A20,&nbsp; T0.MEDIA_ITEM_ID A21,&nbsp; T1.MEDIA_ITEM_ID A22,&nbsp; T2.MEDIA_ITEM_ID A23,&nbsp; T3.MEDIA_ITEM_ID A24,&nbsp; T4.MEDIA_ITEM_ID A25,&nbsp; T0.SYSTEM_ID A0,&nbsp; T0.PATH A1,&nbsp; T0.PARENTDIRECTORY A2,&nbsp; T1.TITLE A3,&nbsp; T1.MIMETYPE A4,&nbsp; T1.RECORDINGTIME A5,&nbsp; T1.RATING A6,&nbsp; T1.COMMENT A7,&nbsp; T1.PLAYCOUNT A8,&nbsp; T1.LASTPLAYED A9,&nbsp; T2.ALBUM A10,&nbsp; T2.DURATION_0 A11,&nbsp; T2.TRACK A12,&nbsp; T2.NUMTRACKS A13,&nbsp; T2.ENCODING A14,&nbsp; T2.BITRATE A15,&nbsp; T2.DISCID A16,&nbsp; T2.NUMDISCS A17,&nbsp; T3.THUMBNAIL A18,&nbsp; T4.THUMBNAIL_0 A19FROM M_PROVIDERRESOURCE T0&nbsp; INNER JOIN M_MEDIAITEM T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; LEFT OUTER JOIN M_THUMBNAILSMALL T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID&nbsp; LEFT OUTER JOIN M_THUMBNAILLARGE T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID WHERE&nbsp; (&nbsp; T2.ALBUM = @V0&nbsp; AND (&nbsp; T0.SYSTEM_ID = @V1&nbsp; OR T0.SYSTEM_ID = @V2))-------------------------------------------------------&quot;V0&quot; [String]: '#1'&quot;V1&quot; [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705'&quot;V2&quot; [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9'[/CODE]Test results:SQL1 (ProviderResource): 4msSQL2 (Artists): 1msSQL3 (Genres): 2msSQL4 (Albumartists): 2msSQL5 (Composers): 1msSQL6 (ProviderResource): 2msNow this finally raised my suspicion that the database - and in particular storing BLOBS such as covers in an SQLite database is definitely not a bottleneck. As you can see, the whole process only takes about 12ms for the database itself INCLUDING loading 11 covers! The covers are loaded in SQL6, which takes in total 2ms. Not really slow....For the files: The cover(s) of these tracks are identical and have a size of 49KB each - so we are reading those 540KB in about 2ms. Will try to find an album with really high res covers and do this test again...Michael</blockquote>

[QUOTE="MJGraf, post: 1028432, member: 17886"] [U]Read Test 4:[/U] Now I enter the first album shown on the screen. This works quite fast - I didn't measure the time, but it was maybe 1sec or so. As a result, I can see 11 tracks of that album - this time [U][B]WITH [/B][/U]covers. Entering this album results in 6 SQL queries: [U]SQL1 (ProviderResource):[/U] [CODE] SELECT COUNT( V.C ) C, V.A0 FROM ( SELECT DISTINCT T1.MEDIA_ITEM_ID C, T0.TITLE A0 FROM M_PROVIDERRESOURCE T1 INNER JOIN M_MEDIAITEM T0 ON T0.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T1.MEDIA_ITEM_ID WHERE ( T2.ALBUM = @V0 AND ( T1.SYSTEM_ID = @V1 OR T1.SYSTEM_ID = @V2 ) ) ) V GROUP BY V.A0 ------------------------------------------------------- "V0" [String]: '#1' "V1" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V2" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] SQL2 (Artists): [CODE] SELECT T0.MEDIA_ITEM_ID A0, T1.ATTRIBUTE_VALUE A1 FROM NM_ARTISTS T0 INNER JOIN V_ARTISTS T1 ON T0.ID = T1.ID INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID WHERE ( T2.ALBUM = @V0 AND ( T3.SYSTEM_ID = @V1 OR T3.SYSTEM_ID = @V2 ) ) ------------------------------------------------------- "V0" [String]: '#1' "V1" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V2" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]SQL3 (Genres):[/U] [CODE] SELECT T0.MEDIA_ITEM_ID A0, T1.ATTRIBUTE_VALUE A1 FROM NM_GENRES_0 T0 INNER JOIN V_GENRES_0 T1 ON T0.ID = T1.ID INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID WHERE ( T2.ALBUM = @V0 AND ( T3.SYSTEM_ID = @V1 OR T3.SYSTEM_ID = @V2 ) ) ------------------------------------------------------- "V0" [String]: '#1' "V1" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V2" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]SQL4 (Albumartists):[/U] [CODE] SELECT T0.MEDIA_ITEM_ID A0, T1.ATTRIBUTE_VALUE A1 FROM NM_ALBUMARTISTS T0 INNER JOIN V_ALBUMARTISTS T1 ON T0.ID = T1.ID INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID WHERE ( T2.ALBUM = @V0 AND ( T3.SYSTEM_ID = @V1 OR T3.SYSTEM_ID = @V2 ) ) ------------------------------------------------------- "V0" [String]: '#1' "V1" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V2" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]SQL5 (Composers):[/U] [CODE] SELECT T0.MEDIA_ITEM_ID A0, T1.ATTRIBUTE_VALUE A1 FROM NM_COMPOSERS T0 INNER JOIN V_COMPOSERS T1 ON T0.ID = T1.ID INNER JOIN M_PROVIDERRESOURCE T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_MEDIAITEM T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID WHERE ( T2.ALBUM = @V0 AND ( T3.SYSTEM_ID = @V1 OR T3.SYSTEM_ID = @V2 ) ) ------------------------------------------------------- "V0" [String]: '#1' "V1" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V2" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]SQL6 (ProviderResource):[/U] [CODE] SELECT T0.MEDIA_ITEM_ID A20, T0.MEDIA_ITEM_ID A21, T1.MEDIA_ITEM_ID A22, T2.MEDIA_ITEM_ID A23, T3.MEDIA_ITEM_ID A24, T4.MEDIA_ITEM_ID A25, T0.SYSTEM_ID A0, T0.PATH A1, T0.PARENTDIRECTORY A2, T1.TITLE A3, T1.MIMETYPE A4, T1.RECORDINGTIME A5, T1.RATING A6, T1.COMMENT A7, T1.PLAYCOUNT A8, T1.LASTPLAYED A9, T2.ALBUM A10, T2.DURATION_0 A11, T2.TRACK A12, T2.NUMTRACKS A13, T2.ENCODING A14, T2.BITRATE A15, T2.DISCID A16, T2.NUMDISCS A17, T3.THUMBNAIL A18, T4.THUMBNAIL_0 A19 FROM M_PROVIDERRESOURCE T0 INNER JOIN M_MEDIAITEM T1 ON T1.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID INNER JOIN M_AUDIOITEM T2 ON T2.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID LEFT OUTER JOIN M_THUMBNAILSMALL T3 ON T3.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID LEFT OUTER JOIN M_THUMBNAILLARGE T4 ON T4.MEDIA_ITEM_ID = T0.MEDIA_ITEM_ID WHERE ( T2.ALBUM = @V0 AND ( T0.SYSTEM_ID = @V1 OR T0.SYSTEM_ID = @V2 ) ) ------------------------------------------------------- "V0" [String]: '#1' "V1" [String]: 'ef2d2144-c347-4db2-b7d2-3e634c6f7705' "V2" [String]: 'f3feaa94-a92c-4ba1-b841-aa617dadefe9' [/CODE] [U]Test results:[/U] SQL1 (ProviderResource): 4ms SQL2 (Artists): 1ms SQL3 (Genres): 2ms SQL4 (Albumartists): 2ms SQL5 (Composers): 1ms SQL6 (ProviderResource): 2ms Now this finally raised my suspicion that the database - and in particular storing BLOBS such as covers in an SQLite database is definitely not a bottleneck. As you can see, the whole process only takes about 12ms for the database itself INCLUDING loading 11 covers! The covers are loaded in SQL6, which takes in total 2ms. Not really slow.... For the files: The cover(s) of these tracks are identical and have a size of 49KB each - so we are reading those 540KB in about 2ms. Will try to find an album with really high res covers and do this test again... Michael [/QUOTE]

Verification