Pages
About Me
Links
Tags
PERSONAL 520
SPIRITUAL 416
LDS 312
BOOK OF MORMON 237
SCRIPTURES 154
STUDIO-JOURNEY 129
RELIGION 112
LINUX 79
COMPUTERS 65
LIFE 60
GENERAL CONFERENCE 46
GENTOO 39
MISCELLANEOUS 37
MUSIC 37
PROGRAMMING 33
CARS 29
MICROSOFT 23
FAMILY 23
AUDIO 21
I LOVE MY JOURNAL 18
FUN 15
CHILDREN 12
CURRENT EVENTS 10
NATURE'S WAY 10
VIDEO 9
DRM 9
CONEXM 7
BABBLINGS 7
PROVO CITY CENTER TEMPLE 6
FRIENDS 6
HEROD THE FINK 5
GAMES 5
COMPUTER HARDWARE 5
DRUMS 4
HAND OF GOD 3
ADVERSITY 3
KDENLIVE 3
AUDIO HARDWARE 3
GENERAL INSANITY 3
STUDIO 3
THANKS4GIVING 2
CATS 2
MY JOURNAL 1
POETRY 1
FOREVERGREEN 1
EVERYDAY THOUGHTS 1
GOSPEL 1
PARENTING 1
YOUTH CONFERENCE 1
CHURCH NOTES 1
POLITICS 1
RSS Feed
Subscribe!Wed - Aug 29, 2007 : 08:56 pm
tired
 |
|
|
|
|
rated 0 times |
A New Frontier with PL/SQL
Man...
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
After talking with thebigdog, he suggested the following:
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.
and that is a shame.
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.
and that is a shame.