Yesterday was a tough day…
I had a call at 02:30 about an extract job (written in Java) that was taking hours to run. After 2 hours of pratting about trying to get it working I gave up and went back to bed. As soon as I got to work I recoded the process in PL/SQL. It was a case of “if it compiles it must work”, because we had very little time to test the process before the next run was required. Fortunately, it worked fine. Did it improve the situation? The original extract took several hours, the PL/SQL version took 23 seconds. Sweet!
What was slowing down the original process? Some bright spark thought it would be better to pull back a huge table into an array and loop through it to searching for data, rather than writing a query to pull back a single row. This action was repeated for every line written to the extract file. There were an assortment of other classic bits of code also, including (the names have been changed to protect the “not so” innocent):
switch (getType())
{
case TYPE_CONST1:
sb.append(TYPE_CONST1);
break;
case TYPE_CONST2:
sb.append(TYPE_CONST2);
break;
}Now, I’m no Java Guru, but for a mandatory item with only two allowed values, I instantly spotted that this actually meant:
sb.append(getType());
Conclusion: If you want data-intensive code to run fast, put it in the database and get someone who understands databases to write it!
Once all the fuss was over, I noticed that the front page of my website wasn’t working properly. It turned out that the OTN RSS news feed was broken, and my dodgy PHP code didn’t trap the error very well. I fixed the error trapping and informed Oracle about the news feed. Within a few minutes the OTN news feed was restored, so all was fine again.
Conclusion: My PHP isn’t as good as my PL/SQL 🙂
Cheers
Tim…
“Conclusion: If you want data-intensive code to run fast, put it in the database and get someone who understands databases to write it!”
Well said! I’d turn it around. First, find someone who understands databases. THEN work together to find the best way.