Since SQLite was introduced in SBS 7.6, I have had some performance problems with Custom Browse and Custom Scan.
1) The most problematic issue was that some of my Custom Browse menus were very slow at times, causing music playing at the same time to be interrupted for quite a while. Several menus took 2 minutes to return data on first selection. After that they were fine for a while. If I ran a slow menu first so that it was subsequently quick, stopped LMS and restarted it, the menu was still quick. However, after a while, without stopping LMS, they got slow again. 2) My Custom Scan times were initially better than on SBS 7.5, but these too got worse, and a full scan was now taking over 3 hours. I have spent a lot of time trying to determine the cause of these issues, and am glad to be able to say that, with Erland's help, I believe have resolved them all. On my system there are two sources of these extended delays. One is the operating system, and the other is the SQL for the menus. I'll deal with my environment and operating system first. GENERAL PERFORMANCE AND SCAN TIMES My PC is a 2 * 2GHz CPU system, about 5 years old but still quick enough for everything I do. It has two new disks, both SATA2, one holding my music and one the software etc. I have getting on for 20,000 tracks, 1,200 albums and over 500 artists. I have the latest Custom Browse plugin (v2.13.3789), and the latest alpha Custom Scan plugin (v3.0pre3781) which is much better at handling a small number of additions or changes). I normally run Windows 7 64 bit Ultimate. I installed VirtualBox and Vortexbox 2 (i.e. running Vortexbox on top of Windows). My Custom Scan times went down from 3 hours to 2 hours. My menus were quicker, but still too slow. I installed a dual-boot Ubuntu 11.10 system (i.e. running pure linux). My Custom Scan times went down to 30 minutes. Menu times are even better, but I still had 30 second delays. The overall performance of LMS is much, much better with a stand-alone linux-based system, so I shall probably be investing in a separate system (possibly a VortexBox) at some point, not possessing a spare PC to use. However, negotiations with my better half may take some time! I won't suggest that such installations have been without pain. I'm not that good with linux, and many problems with LMS had me stumped for many hours. It was, however, worthwhile in the end. MENUS My music tags and related Custom Browse menus are designed for classical music. They are very closely based on those proposed by Erland. See http://wiki.slimdevices.com/index.php/Setup_browse_menu_for_classical_music. The main addition is that I have added an additional Custom Tag 'WORKARTIST', which is the primary artist for a WORK (i.e. for a selection of MOVEMENTS). This allows me to distinguish between the primary Artist on an ALBUM (ALBUMARTIST) and that for a track or MOVEMENT (ARTIST or TRACKARTST). I have several menus based on the 'Works by Composer' menu, all of which had the same or similar performance problem. The key section of that menu is the top level menu section, that originally looked like this (the formatting below is a bit odd and contains spurious spaces - the original doesn't): <menudata> select customscan_track_attributes.extravalue,customscan_track_attributes.value, substr(ifnull(customscan_track_attributes.valuesort,customscan_track_attributes.value),1,1), ifnull(customscan_track_attributes.valuetype,'mixedtagCOMPOSER') from customscan_track_attributes join multilibrary_track on customscan_track_attributes.track=multilibrary_track.track and multilibrary_track.library=2 join customscan_track_attributes as attr2 on customscan_track_attributes.track=attr2.track and attr2.module='mixedtag' and attr2.attr='WORK' join customscan_track_attributes as attr3 on customscan_track_attributes.track=attr3.track and attr3.module='mixedtag' and attr3.attr='WORKARTIST' where customscan_track_attributes.module='mixedtag' and customscan_track_attributes.attr='COMPOSER' group by customscan_track_attributes.extravalue order by ifnull(customscan_track_attributes.valuesort,customscan_track_attributes.value) asc </menudata> This menu item reads all COMPOSERs that are in the Classical Multi-library (I have 2 libraries, one Classical and one for every other genre), have associated WORKs, and associated WORKARTISTs. That means that a lot of my Custom Scan database must be read and linked to other records in the same database. After a lot of trial and (mainly) error I realised that most of this is unnecessary. Looking at the 'joins': a) I don't care if a COMPOSER is listed in the menu and has no WORKs or WORKARTISTs - I'll find out later on. b) None of my non-classical stuff has COMPOSER tags. All the 'joins' can therefore be disposed of. Two further tweaks I applied because using functions in the SQL probably slows it down. So: c) the 'valuesort' attribute always seems to be set (by the Custom Scan module) in the underlying tables (even though I don't use any sorted tags), so the ifnull function in the sort order at the end is not required. d) the 4th argument in the first selection (valuetype) does not seem to be needed for the top level menu (it may be needed once one gets down to a menu level where something can be played) e) but I'll keep the parameter that shows the first character of the COMPOSER since that allows one to use the first letter of the name to skip through the list on the Touch I am now left with: <menudata> select customscan_track_attributes.extravalue,customscan_track_attributes.value, substr(ifnull(customscan_track_attributes.valuesort,customscan_track_attributes.value),1,1) from customscan_track_attributes where customscan_track_attributes.module='mixedtag' and customscan_track_attributes.attr='COMPOSER' group by customscan_track_attributes.extravalue order by customscan_track_attributes.valuesort asc </menudata> My top-level menus are now almost instant on linux, and take 20-25 seconds on Windoze (still too slow), so I'll head on to linux. Finally, note that although the lower levels of my menus are unchanged and still contain many joins, they are not slow in any environment. I assume this is because all the data has already been read into memory, and the range of items being read is much smaller (e.g. only things for one COMPOSER). Don't ask me why SQLite on Windows is so much slower than MySQL for these functions (but quicker for the core LMS/SBS scanning functions). I have no idea. I hope the above is some help to someone out there. -- PasTim Server on Windows 7 Ultimate 64 bit, 2 CPU, 2GHz, 4GB, FLAC files. Touch on Ethernet (in another room). Analogue out over 'a bit of wire' to ageing Quad Hi-Fi. An old (wireless) laptop controls the server using Chrome. ------------------------------------------------------------------------ PasTim's Profile: http://forums.slimdevices.com/member.php?userid=41642 View this thread: http://forums.slimdevices.com/showthread.php?t=93699 _______________________________________________ plugins mailing list [email protected] http://lists.slimdevices.com/mailman/listinfo/plugins
