"About two years ago," Simon writes, "I worked for a small telecommunications company. Turnover was fairly high, leaving not much consistency in the way applications were developed."

"Shortly after starting, I was tasked with fixing a report. For whatever reason, it wouldn't show any data newer than two months. So, I went to the reporting website and loaded up the report page. Eight minutes of watching the hourglass cursor turn, I verified that the report did not, in fact, display any new data. So I dug in to the report code.

"What first greeted me was a few hundred lines of integer declarations...

int iCompany1Nov2003 = 0; int iCompany2Nov2003 = 0; int iCompany3Nov2003 = 0; int iCompany4Nov2003 = 0; int iCompany5Nov2003 = 0; int iCompany6Nov2003 = 0; int iCompany7Nov2003 = 0; int iCompany1Dec2003 = 0; int iCompany2Dec2003 = 0; int iCompany3Dec2003 = 0; int iCompany4Dec2003 = 0; int iCompany5Dec2003 = 0; int iCompany6Dec2003 = 0; int iCompany7Dec2003 = 0; ... snip ... int iCompany5Dec2009 = 0; int iCompany6Dec2009 = 0; int iCompany7Dec2009 = 0;

"Continuing down the code file, my next discovery was this...

iCompany1Nov2003 = getTotals(con, "Company1", 11, 2003); iCompany2Nov2003 = getTotals(con, "Company2", 11, 2003); iCompany3Nov2003 = getTotals(con, "Company3", 11, 2003); iCompany4Nov2003 = getTotals(con, "Company4", 11, 2003); ... snip ... iCompany5Oct2005 = getTotals(con, "Company5", 10, 2005); iCompany6Oct2005 = getTotals(con, "Company6", 10, 2005); iCompany7Oct2005 = getTotals(con, "Company7", 10, 2005);

"And below that was a few hundred more lines of commented-out calls to 'getTotals()'. It went all the way to December of 2009. It should come as no surprise what getTotals() was like...

int getTotals(dbCon, companyCode, month, year) { rows[] = dbConn.exec( "SELECT * FROM company_trans " " WHERE companyCode = ? AND month = ? AND year = ?", companyCode, month, year); return rows.length; }

"So, if you're keeping count... for the basic report, we had a total of 168 individual queries on the (unindexed) company_trans table, which contained a good 3 million+ rows. Of course, that was just the code to get the data; beneath all the queries was code to display the results. Want to guess what that looked like?

... out( "<td>" + iCompany1Nov2003 + "</td>" ); out( "<td>" + iCompany2Nov2003 + "</td>" ); out( "<td>" + iCompany3Nov2003 + "</td>" ); out( "<td>" + iCompany4Nov2003 + "</td>" ); out( "<td>" + iCompany5Nov2003 + "</td>" ); out( "<td>" + iCompany6Nov2003 + "</td>" ); out( "<td>" + iCompany7Nov2003 + "</td>" ); out( "<td>" + (iCompany1Nov2003 + iCompany2Nov2003 + ... + iCompany7Nov2003 "</td>" ); ...

"... and so on. All told, the simple report spanned well over 3,000 lines. Fortunately, all I had to do was uncomment the two months and pray that someone else would get stuck with it next month!

"Just kidding. I rewrote the report to use a simple GROUP BY query, added a loop to display the results, and managed to get it running in less than three seconds.

"When I showed my lead the old code and the new, he responded, ah, that must have been Jed Code; yeah, he really hated anything that had to use arrays or loops, he couldn't see the point of them ... I think each month he would uncomment the next month and redeploy the application

And in case you're wondering... no, it wasn't that Jed. Clearly, there weren't enough comments.