Over the course of working with one employer, I headed up an investigation into our current server performance, and how would could potentially aim for at least a 25% to 30% increase on our current user thresholds before our capacity maxed out.

After looking through a series of server logs, and data transactions it was easy to see that the server technology being used, Coldfusion, is not the most efficient data handler in the world.

So after stripping back our application layer, taking a peek under the hood, and changing around 600 or so instances of data connectivity from inline SQL code, to Stored Procedures, we had more than exceeded our target gain. In fact under stress testing we had achieved a consistent increase of around 65% threshold. As a quick set of figures, the server loads had changed from CF, running at 75%-80% dropping to 20%-25%, and SQL server running at 6% increased to around 15%.

Based on these figures, and architectural methodologies I've learnt since, I would always recommend stored procedures over inline SQL code. This has lead me to develop a standard set of SQL templates for developers to use.

They are self checking, repeatable SQL templates that will action the desired changes, and check for any existing conditions up front. The idea is that a non technical resource could run them, and receive a useable English response.

1. Select statement
2. Update Statement
3. Insert statement
4. Delete statement