Blog of Dave

Addiction: “Strong dependence or habitual use of a substance or practice, despite the negative consequences of its use.”

We are working on refactoring an old ColdFusion application that allows multiple companies to offer a custom and fairly complex application form for for their customers (applicants). In this application, an exceptionally large amount of data is collected from the applicant. Depending on the options that the customer selects to require from their customers, this can work out to be up to about 400 form fields. The designers of the application specified that it must consist of 9 tabbed pages, broken apart by like questions.

To preface, this application was written right about the time that CFMX 6 was released. The only use of CFCs is completely opposite to what has become best practice since that time. In this application, CFCs are not objects, but are more or less repositories for related UDFs, many of which are used as views to display sections of the application.

From an application flow perspective, the main form template which is the meat of the application is a single CFM file with a switch/case block that then includes the appropriate fields that the user should see (when it isn't throwing exceptions) for whatever section they are in on the form, with each tab being a new request to the server. On each request, the application will make anywhere from 20-50 queries against the database to populate any data to the fields on the tab the applicant us on. To make matters worse, as a user works their way through the application, leaving a tab creates a submission and adds on another 20-50 insert/update/delete queries.

In addition to the data retrieved related to the User's Application, each request forces the server to query the database to find out settings about the company that servicing the applicant. That is another (get ready for this...) 30-40 queries. It is very typical in this application for applicants to have to wait 4-7 seconds in between pages.

So this is a very high level of the problem... Put your thinking caps on and see what solution you come up to reduce this nonsense. Next I will share ours.

Next: How I Broke My Database Addiction: Part II - The Solution