Hey guys, Looking for some database help here.
Basically, if you have a LARGE media library, say, 20,000 pieces of audio, and you hit Play All to play them all, there is a considerable amount of processing time that happens, before anything appears to happen from the user's perspective.
This is caused by this snippet of code:http://pastebin.com/LqWuDeDy
(all discussion here focuses on the pluto_media database)
What's happening here, is:
* When a user hits Play All, it sends an MH Play Media command to the Media Plugin, the filename is !Gxxx, e.g. !G250, that is, the current mediafile grid for Orbiter #250.
* The code snippet catches the special !G case, and immediately embarks upon a loop to traverse through the table, grabbing the cell values.
* For each cell you see, there is the text you see, and the actual value passed back to the code, and depending on which view you see, you can either be looking at a pile of Attributes (Director, Genre, etc.), or at a pile of files (the Title view, Filename view), so you'll see e.g. a set of !F1234, !F1235, or !A1234, !A1235, for PK_File 1234, 1235 or PK_Attribute 1234,1235 respectively.
* Then there are the two if's... for files, our job is easy, we get the appropriate row from the File Table, and put together the directory and file path, and then pass that along.
* For Attributes, this is more involved. We need to get the Attribute, then we need to match that against entries in the File_Attribute table, and grab all the matching PK_Files for each PK_Attribute, and THEN do what we mention above.
* At this point, the returned full path to the file is then passed into a new MediaFile object, and then added to the deque of MediaFile objects to become the playlist.
* Then the process repeats, until ALL of the cells have been traversed.
This process involves a potentially large number of individual queries the size of toothpicks. These toothpicks are small, and are inconsequential for the database to query and return, but, we also have the process of taking that data, peeling it apart, and acting upon it by doing more tiny queries, in a loop, all of this happening serially, and therefore incredibly inefficiently.
Why does this matter?
Well, besides the obvious effect of longer and longer delays from the user's standpoint, any code built on DCE has a built-in deadlock prevention timer. That is, if you send a message, and that message takes longer than 30 seconds to complete, DCE will assume that things have stalled, and the offending process will be terminated. Since Plugins like the Media Plugin run in exactly the same address space as DCERouter, that means the DCERouter gets killed if some work in a plugin takes too long to complete! Router goes "bye-bye;" whole system says, "sayonara."
So even if you can PUT UP with the egregious delay while asking the system to play a large number of media, there will hit a point where your system will take too long to process the "Play All", and your system will appear to crash. First, your Orbiters, then the core. It will recover, but your intended goal of playing the media won't happen.
Ok, so, what's the goal?
We need to make this block of code more efficient, and the way I can see that happening, is to make a proper database query to return ALL of the PK_File entries that match either the list of !F or !A entries that are present in the currently visible datagrid. This can subsequently be peeled apart linearly into MediaFiles and dequed appropriately. I believe this will decrease the delay to less than a few seconds, if not less than 1 second, for 99% of cases,
but, I am not an SQL wizard, and need some help to try and construct this query.