Improving Performance of Play All

[tschak909]

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.

Any thoughts?

-Thom

[tschak909]

I am currently using the query:

Code: [Select]

SELECT File.Path, File.Filename from File, File_Attribute
WHERE File.PK_File = File_Attribute.FK_File AND FK_Attribute IN (1,2,3,4,5,6...) AND Missing=0 AND IsDirectory=0;

Once I have a list of attributes. I know an inner join can be used here, because we're only interested in attributes that have direct file relationships.

-Thom

[jamo]

Thom...  just a thought, apologies if you've been down this route already...

Should we not go back a level above this? What I'm thinking is that we look at the process that populated the datagrid in the first place. Presumably there is a sql query that is used to return the list that is currently in the DG. If we can dive in there and record that sql query (possibly as a property of the DG object) then when the user hits "Play All" we pull the query and possibly combine it to form a query that returns distinct entries from the "File" table. Then we loop through those rows to populate the playlist queue.

[Marie.O]

* posde thought the same thing that Jamo posted.

[tschak909]

Possibly. you would need to look at the media browser code in Media Plugin Grids, to see how the query is constructed. Take a look at the code and let me know your thoughts.

At least with my current approach, we can see an order of magnitude difference, and I already know how to implement it.

-Thom

[WhateverFits]

Inner joins (or left outer joins) are the way to go here to speed up this chunk of code. If you need to implement some logic that doesn't work with just a simple join, subqueries are MUCH faster if executed as part of a stored procedure. I try and keep logic away from the SQL server but I've had to do it in many locations and it improves performance dramatically. The logic here seems simple enough to not have to resort to a stored procedure though.

Also, once you have the query written, write a specific index just for that query. Do you need help writing the query? If so, just give me a list of the columns necessary and I'll put together a query for it.

[tschak909]

I _think_ what I have pasted above will work.

In the end, all I need, is a media type, a path, and a filename. The last two will be CONCAT()'ed together, with a /, producing the file path to insert into MediaFile.

If i'm right, this should bring performance to below the 1 second level.

-Thom

[WhateverFits]

Running your query on my box executed between 0.22 and 0.52 seconds on multiple executions with 47849 results. Not a huge list, but big enough to test.

[tschak909]

Fantastic.

[jamo]

Possibly. you would need to look at the media browser code in Media Plugin Grids, to see how the query is constructed. Take a look at the code and let me know your thoughts.

At least with my current approach, we can see an order of magnitude difference, and I already know how to implement it.

-Thom

Hi Thom... if the query works, go for it!

[tschak909]

As it turned out, the new code only shaved three seconds off the query. The problem lies in the MediaFile constructor itself. Namely, that when passed a filename, pk_file, and MediaAttributes_LowLevel object, the constructor uses the latter, to fill in some of the Media file members. This is accounting for 90% of the time spent inside the function, and ultimately it is a microcosm of the problem I originally tried to solve, that is, picking up toothpicks, one for each attribute wanted, one left join per call....

Further optimization is needed, if you want to see where, look at MediaFile.h in Media Plugin.

-Thom

[jamo]

So you're saying that we run a "big" query to get all the files from the datagrid and then we *requery* the database for each one to populate the members of the MediaFile object? Is that right? And you're suggesting that if we pick up the relevant data at the time of the "big" query and just push it straight into the members without having to requery each one....

I saw the constructor of MediaFile in MediaFile.h but where is the actual code for the constructor?

[tschak909]

It's all there in the .h. MediaFile is basically a model, keeping state, so it doesn't need much more than the constructors.

The constructors are overloaded for different needs, e.g. for copying mediafile instances, or when attributes are needed a MediaAttributes_LowLevel instance is passed.

-Thom

[tschak909]

Interestingly enough...

Once I commented out the calls to get the low level attributes in MediaFile.h, the adding to the deque happens now in a little over a second, however, a bottleneck still remains, I am still seeing roughly 10 seconds of delay, before the subsequent calls from Orbiter to request datagrid table.

-Thom

[tschak909]

Digging further, approximately 3 and a half seconds of that delay, happens in StartMedia(MediaStream *pMediaStream), the generalized start media method for all MediaHandler streams. It is caused by repeated calls to MediaAttributes->LoadStreamAttributes(), which populates a given mediafile with all the attributes attached to a piece of media. This can't be helped much, so I am looking elsewhere to try and get rid of that initial 10 second bump while populating the deque... The thing that confuses me, is that if i remove the parts of the code that cause the bottleneck, the bottleneck shifts elsewhere...

Really need a few more pairs of eyes to look at all of the overloaded StartMedia methods in the Media_Plugin.

-Thom