At work, I've got .... well.. I was gonna say dilemma, but... it's not a dilemma because I know of a way out.
My dilemma is, I've gotta have a website functional by Friday, which means I've got two days to get it to work.
The database we're connecting to (postgres on gentoo, thank heavens) has a table with more than 12 million rows in it. The query I'm running on the home page of this website provides statistics which require me to poll every single one of the 12million rows.
I understand indexing, and have made sure the queries are pulling from indexes, and not doing sequential scans. That being the case, the page still takes anywhere from 8 to 20 seconds to load, which is completely unacceptable. This site isn't gonna need to be a high availability site as it will only get hundreds of pageviews per day, but taking that long to load the home page is not gonna work.
After talking to the wonderful people in the #UPHPU channel on freenode (a guy who goes by the name "thebigdog" was especially helpful), I have a couple of options. Before I only had one. That option was
- Create a caching table which pulled in all required data once-per-day and create the homepage from that table. This would *definitely* get the load time down to much lower than acceptable times. More around the arena of 20-30 milliseconds.
After talking with thebigdog, he suggested the following:
- Implement a function written in PL./SQL to draw from the tables instead of writing a massive query with multiple sub-queries and joins. This would also bring the load-times down to acceptable times - and would allow a much greater flexibility for future scaling of the home-page.
So... I spent the larger portion of today trying to jam PL/SQL down my throat. It didn't go down so well. We'll see if I can't get my head around it by tomorrow afternoon. Because it's now getting down to the wire for this app to work.
What I'll probably end up doing is creating the caching table to get it done quickly, and then spending time later on to get the appropriate function written when I have time to spend on it.
Yup... I think that's what I'm gonna do.
Oh, and I think that darn cryptosporidium parasite is finally reaching the end of its days in my entrails. I so wish
I could put that darn critter in the crosshairs of some high-powered rifle and watch it be blown to tiny bits, but unfortunately, life just doesn't work that way with these buggers.
is a shame.