But did the original DBA who set it up wrong in the first place retain his job? If yes, the problem was not solved. But they get a very good workaround solution :)<br><br><div class="gmail_quote">On Tue, Jul 8, 2008 at 10:32 AM, Paul Cartwright <<a href="mailto:ale@pcartwright.com">ale@pcartwright.com</a>> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">I remember Pyramids in DC, US Gvt bld, up next to our AT&T 3B5's.. not to<br>
mention Informix.. ok, so it ISN'T Linux.. this was b4 linux!<br>
<a href="http://thedailywtf.com/Articles/Hastening-an-Inevitable.aspx" target="_blank">http://thedailywtf.com/Articles/Hastening-an-Inevitable.aspx</a><br>
<br>
Hastening an Inevitable<br>
2008-07-08<br>
by Jake Vinson in Feature Articles (17 Comments)<br>
<br>
Keeping hundreds of millions of sheets of paper on file isn't easy, so the IRS<br>
had an application built to computerize their records. It'd scan paper tax<br>
returns into a WORM (Write Once, Read Many) drive system and record lookup<br>
data in a database. That way they could filter by any fields they recorded in<br>
the database and access a scanned image of the tax return for any further<br>
information using a simple app, which sure beat the old method of data<br>
retrieval — digging through boxes, incurring huge wait times.<br>
<br>
The nice thing about the old method, though, was that it generally worked. The<br>
new system was full of bugs, in addition to several other irritating issues.<br>
On Bobby's first day he was put in front of the application, and right off<br>
the bat it looked amateurish. Form elements not lined up properly, buttons<br>
not always the same size, inconsistent menus — not broken, but certainly not<br>
professional looking. His boss, Boris, explained some of the finer features<br>
in a dry, humorless, low monotone.<br>
<br>
"Now, as you can see heeere," his boss mumbled, "this is pretty exciiiting."<br>
Boris would linger on certain syllables presumably in an effort to sound more<br>
boring. His tone made Ben Stein sound like Freddie Mercury. "Allll we have to<br>
do is click a feeew buttons annnnd..." Meanwhile he was typing some text into<br>
the fields to perform a lookup. "And affffter a few short minnnnnutes..."<br>
<br>
Bobby was briefly impressed — Boris had made a joke! Not a particularly funny<br>
one, granted, but certainly worth a polite chuckle... except- wait a minute.<br>
This is actually taking several minutes, Bobby realized. And in Boris's<br>
company, each passing minute felt like ten. Boris whiled away the minutes<br>
describing some of his hobbies — toy piano tuning, model taxidermy,<br>
palindromatic haiku composition — each somehow more boring than the last.<br>
After an eternity (nine minutes) of struggling to keep his eyes open and<br>
subtle closed-mouth yawning, an error message popped up.<br>
<br>
"Ohhh, fidddddlesticks," mumbled Boris in lifeless anger. As Bobby would<br>
eventually learn, the database's average response time for tax form searches<br>
was in the realm of 8-10 minutes. That was if you were fortunate, though —<br>
it'd often just time out.<br>
<br>
Although Bobby wanted to help, he wasn't allowed to lay a finger on the<br>
database. He was only authorized to edit the frontend code. So to start off<br>
he gathered whatever information he could — the hardware (Pyramid<br>
Minicomputer running four 150Mhz processors) and the software (AT&T Unix and<br>
Informix DB server). He looked through the code and made a few UI<br>
improvements, and it didn't take long for him to locate the biggest<br>
bottleneck — the database.<br>
<br>
After getting in touch with the DBAs, Bobby was granted read-only ODBC access.<br>
Bobby kept making tweaks and little optimizations, constantly running SELECT<br>
queries, but couldn't get them to run any faster than six minutes. He dug<br>
through ODBC settings, hoping to find something... anything that he could<br>
change to improve performance, but judging by the crazy, arcane settings<br>
already set up, many had tried the same thing and failed.<br>
<br>
Bobby had spent several hours and not gotten far from where he started.<br>
Wanting something to show for the time spent, he threw together a<br>
quick "Please Wait" dialog box with an animated hourglass to give the bored<br>
operators something to watch while they waited for the data to come back.<br>
<br>
Several days later, Bobby had a stroke of luck. One of the higher-ups had<br>
caught wind of his UI changes and wanted a demonstration. When the topic of<br>
speed came up, Bobby mentioned that he'd found the bottleneck and suggested<br>
he get full access. It shouldn't be a problem since he worked under constant<br>
supervision anyway.<br>
<br>
"I don't think that'll be an issue," he said with a smile. "Can you do it<br>
right now?"<br>
<br>
Bobby hesitated, then gave a weak "...sure." He flushed a little red,<br>
concerned that he was wrong and he'd embarrass himself in front of everyone.<br>
A DBA was called to the room, who opened up a console window and turned Bobby<br>
loose.<br>
<br>
Of course, it's highly unorthodox for someone to be given absolute access to<br>
one of the IRS's production databases, but no one wanted to question the big<br>
cheese. The DBA watched Bobby like a hawk.<br>
<br>
Bobby swallowed the lump in his throat, tried to ignore the seven sets of eyes<br>
on him, and typed some simple queries in an attempt to isolate the problem.<br>
In short:<br>
<br>
* Indexes: none<br>
* Primary keys: none<br>
* Rows: ~2M per table, four or more joins per query<br>
* Processor speed: 30Mhz<br>
<br>
Bobby fired off a few commands to create indexes on fields that he knew were<br>
frequently queried. The DBA's stare intensified. Bobby felt more nervous,<br>
hoping that it would all work ok. And after verifying that queries were still<br>
being profiled, he ran a SELECT query.<br>
<br>
It took 0.22 seconds.<br>
<br>
Crap, he thought. There's no way it could've run that fast. I must've totally<br>
destroyed the production database. Wiping sweat off his forehead, he ran the<br>
query again, this time to verify the results. And the results were correct.<br>
<br>
With each query he ran his smile grew larger, as did the rest of the room's<br>
collective disbelief. The "Please Wait" window would appear and hide so<br>
quickly that no one could even see it.<br>
<br>
They tried to chart the difference, but what's the point? 8-12 minutes versus<br>
0.35 seconds average? Management did all but break out the pom-poms and do<br>
cartwheels throughout the office. The operators were so ecstatic that they<br>
actually threw him a party. As for us US taxpayers, we benefited as well<br>
since we're getting taxed faster!<br>
--<br>
Paul Cartwright<br>
Registered Linux user # 367800<br>
Registered Ubuntu User #12459<br>
<br>
_______________________________________________<br>
Ale mailing list<br>
<a href="mailto:Ale@ale.org">Ale@ale.org</a><br>
<a href="http://mail.ale.org/mailman/listinfo/ale" target="_blank">http://mail.ale.org/mailman/listinfo/ale</a><br>
</blockquote></div><br><br clear="all"><br>-- <br>-- <br>James P. Kinney III <br>