My friends know I am a huge fan of the tv show The West Wing, hence the title of this post. As I was writing my post on using stored procedures, this one slapped me in the head and began forcing its way out. Too often developers think their language of choice is the end-all be-all of the development world. As a result they force it to do every necessary function when sometimes other tools, already readily available, can provide not only a better method, but can lead to exponential performance gains.

As I continue to shape my development methodologies. I've adopted the attitude of "let the experts do what they are expert in". In other words, just because Coldfusion can do something does not mean it should be the component to do it. SQL Server (or Oracle or mysql) is the master of the data I want to use. It knows how to store it and retrieve it and parse it. So whenever possible, I want SQL to handle as much pre-processing (and post-processing) of the data as possible and let Coldfusion handle the responsibility of interfacing with the user and processing requests. Additionally, I don't want to get invalid data from the server that then takes processing to correct before using.

For example, I had an instance where I needed to manipulate the pricing of inventory based on a client variable. Initially, this processing was being done by CF after SQL Server sent the inventory information back. While this certainly works, the concept of knowingly retrieving invalid data and then making it correct is never a good idea. So I adjusted my stored procedure to accept 1 more parameter and modified the logic inside to do the price adjustment. This change maintains server responsibility and prevents Coldfusion from having to fix invalid/incomplete data.

Obviously that is a simple example that only leads to a small performance gain; maybe even negligible in the grand scheme. But what if I have a small set of data, say from a form submission, where some values cause a table update and other values must be inserted across multiple tables? Is it more effective to write the conditional logic and queries in coldfusion and then have the server make multiple requests to SQL to make it happen or pass all the variables into a single stored procedure that encapsulates the logic and acts upon the locally stored data?

If the Coldfusion server's request responsibility it dropped from, say 3 insert queries, to 1 stored procedure call that is a 66% processing reduction each time that template (or component) is processed. Spread that across multiple simultaneous requests (maybe thousands) and you begin to really see the benefit. Of course, this also makes more resources available overall which benefits all threads on the Coldfusion server.

Imagine that, letting a more apt resource handle what it handles best when necessary, you get improved performance all around.