Oracle CLOBs in Rails

Now that the application I have been working on at JPMorgan Chase is live in production, my team has been looking into performance. In less than a month over 5,000 employees have logged in, so working on optimization is no longer pre-mature.

By parsing the logs daily, we focus on improving the slowest URLs which are hit most frequently. One page in particular was taking about 30 seconds to load. Here are some interesting numbers related to how long it takes the page to load given network latency between the app and db servers:
26ms: ~32 seconds
13ms: ~16 seconds
1-2ms: ~2 seconds

We began looking into the latency after noticing differences between performance in our various environments. Although some other processing takes place, this particular page loads 200 records with 3 fields: an id, name, and description. The description was a CLOB - the default type from a Rails migration using :text for the column type.

What happens if we switch that CLOB to a VARCHAR2(4000)?
26ms: ~6 seconds
13ms: ~3 seconds
1-2ms: ? (untested)

That is a huge performance benefit from changing nothing but the SQL datatype. Here is the code I used to write the DB script. Of course, developers in the enterprise cannot update the production database; otherwise, I would have written the rake task to update the DB automatically.