id	summary	reporter	owner	description	type	status	priority	milestone	component	version	severity	resolution	keywords	cc	mlocked
10047	Optimize Mythweb Program Listing	fracmak@…	Rob Smith	"So I've been working on what I thought would be a simple science project and has kind of taken on a life of it's own. I've been investing why the program listing page seems to take about 2-3 seconds to load on a Intel Core 2 Duo processor at 0 load. My setup has about 200 channels, which generates about 200k program entries, 2800 oldrecorded entries. 

My investigation has shown what averages out to be about 2600 db queries on each page load. The flow seems to go as follows
  1: get callsign list
  2: for each call sign, request channel from db (200 queries)
  3: for each channel, request a list of programs program (give or take 4 per channel, so 800 queries)
  4: each program preloads a channel from the db (800 more requests)
  5: each program needs to check the oldrecorded table (800 more requests)

Each query is fairly fast, taking between 1-4ms, but when you add all that up, you get almost 3 seconds of load time. The advantage of this method though is that you can start streaming the html to the client fairly fast, so you get the illusion of speed, but you quickly overwhelm the db server as you start navigating around the program listing.

I've rewritten this entire process so it boils down to 2 queries, which are much easier to optimize. The first query is the heavy lifter. It loads up the entire program listing of your view combined with the channel information for display in one full swoop. After much poking at it, I was able to get the performance of that query to come in at about 500ms on my machine, which still seems high, but is a full 300-400% improvement over the old method. There's one downside to this query, to get the performance this far down, I had to put an extra constraint on the query. The old query had a where clause of program_start_time < display_end_time && program_end_time > display_start_time which worked out well cause it no matter how long a program was, you'd capture it if it crossed the display you were looking at. Unfortunately this has a HUGE performance hit on the db due to the way the indexes work. I did some metrics on my data and found that no program is longer than 8 hours, so I put a cap on the start_time to be 12 hours, just to be safe. This cut down a 1.2s query to 500ms, which I think is worth it. If we find a program that's longer running, I can easily up that number. The second query is one of the oldrecorded queries that I found extremely hard to integrate into the main query without destroying the performance, so it's broken out into it's own query. I added these 2 queries as a new simplified version of load_all_program_data() called load_program_list(). I also rewrote list_data.php from a channel loop followed by a program loop, to just a program loop that has a variable to track when a channel changes. I've removed a bunch of logic in my attempt to overhaul the loop and simplify the code, so I'm certain I've messed something up for someone, so there's much testing to be done (but it seems to be working fairly well for my on my machine). 

Now the bad news, to fix #4 above, I had to comment out an assumption that if a chanid is passed into the program object, it'll construct a channel object from the db. I don't particularly like this assumption, because it assumes you always want to have that db hit for each program entry you build (this assumption exists for Schedule and Recording objects as well), which is detrimental to the program listing performance. I've already found two function calls that depended on this assumption, the ajax get_show_details called from the program listing page, and the program details page. 

So I decided to stop here, until I could detail out what I've done and get feedback. I'd love to dig in more to the mythweb project and try and clean up/streamline/simplify some of the functionality. I have a number of ideas on how to break apart the themes from the underlying code to make it easier to skin mythweb and make building new functionality easier, but I figured I'd get the blessings from the mythweb gods, and make certain that what I've done so far meets with their approval. I've uploaded my changes to a github fork of the mythweb project for everyone to see:

https://github.com/fracmak/mythweb/tree/listings_optimization_scratch
"	Developer Task	closed	minor	unknown	Plugin - MythWeb	Master Head	medium	Won't Fix			0