DevDisasters

Weekend Drive

• By Alex Papadimoulis
• 02/01/2010

Suddenly, and for no apparent reason, an internal support application at Joe R.'s company suffered a slowdown in executing queries and returning data. As the programmer tasked with working on this particular .NET 2.0-based Web app, this sluggish performance affected Joe, as his client-service software was now "unusable."

It was assumed that the root of the problem was Joe's code. However, Joe knew this was unlikely because the code had been working fine when he had left for the weekend.

Tuning the System
When Monday morning arrived, Joe was informed of the application performance issues and the painfully slow responses from the Oracle database servers. He tried everything he could think of to remedy the performance problem: code optimization, query optimization, re-compiling views. Everything. Still, no dice.

The performance issue was making Joe's internal support application run exceedingly slow, and it needed to be fixed. Identifying the root cause kept him busy for most of the day, and the next day, and the next. He even tried executing the SQL Queries directly in his SQL Code editor.

The queries were still very slow. Queries that should have taken milliseconds were taking roughly 30 seconds to execute:

select field1, field2, field3,
    (select field_a from table1
where field_b = a.table_1_id) 
field_a,
    b.field4, a.field6,
    (select field_c from table2
where field_d = a.table_2_id)
field_c,
    (select field_e from table3
where field_f = a.table_3_id) field_e,
a.field7, a.field8,
a.field9 AS "the_field_9",
    (select c.field_g from table4 c 
where c.field_h = a.table_4_id) as "the_field_g",
    (select c.field_g from table4 c 
where c.field_h = a.table_5_id) as "the_field_h",
    (select c.field_g from table4 c
where c.field_h = a.table_6_id) as "the_field_i",
    a.field10, a.field11, a.field12,
    (select d.field_j from table5 
d where d.field_k = a.table_2_id 
and d.field_j = 'My Value') as "the_field_k",
    (select d.field_l from table5 d
where d.field_m = a.table_2_id and
d.field_j = 'My Value') as "the_field_o",
    a.field13, a.field14, a.field15, a.field16,
    (select field_p from table6
where field_q = a.field_r)
field17
    from table0 a, table6 b
    where b.field_s = a.field_t

By this point, Joe had circled back to his original conclusion: It was nothing that he did because everything was working fine the previous Friday. So he knew that something happened over the weekend that he had no control over.

Watch Where You Put That!
Enter the boss' son.

For some reason, the boss' son had put all of the databases on a thumb drive. He was planning on moving the system to another server, and had accidentally put the entire thing on a USB drive.

Whoops.

Once Joe discovered this, the system was placed on the proper server and everything went back to executing smoothly. As far as Joe could tell, the higher-ups never actually said anything to anyone affected by this slip up -- especially the other developers who worked more closely with the DBMS. They just acted like nothing happened.