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

I was recently looking at a report generator, and came across a conflict in the mySql query dealing with the date objects.

The report returns a set of records between two dates, using cfqueryparam, but there seemed to be a problem in running the query using certain dates.

Running it from a browser provided differing results from running it directly in a sql interface. So where was the error?

The original code was using the "cf_sql_date" type to pass the date time off to the sql engine. It appears that this data type is not transferred as a date time value, but as an incompatible numeric value, that is not comparable to the mySql datetime I am querying against.

There are a wealth of articles online about passing date values into a variety of database technologies, but this Adobe post was very helpful.

This article shows a handy table of cfsqltypes and how they map to different database management systems.

Using something like sql profiler you can clearly see the difference between cf_sql_date and cf_sql_timestamp, where cf_sql_date is being truncated to just a date value (actually evaluates as a numeric value) and a full date time stamp that cf_sql_timestamp generates.

Changing the code to the above example completely resolves this issue.

An easy way to create a scrollable contained area within your template is to use the overflow style property. This can be set to true or false, and also auto, which will add a scroll bar, and allow scrolling if the content exceeds the dimensions of the div. The DIV code which goes inside your template. This div can be placed inside any element of your page including < td > or other < div >'s.

Text thanks to http://www.lipsum.com/