I was curious about the speed comparisons between an MS SQL Stored Procedure called through ColdFusion, a typical ColdFusion query written in CFM, and a ColdFusion query written in CFSCRIPT. A query of five (5) columns and 58,960 records was chosen. Here is the code used:

<cfstoredproc procedure="GetTestC" datasource="TestData" result="GetResultC" />
<cfdump var="#GetResultC#"> SP<br />

<cfset TestSQL="SELECT * FROM TableName">

<cfquery name="GetTestA" datasource="TestData" result="GetResultA">
   #TestSQL#
</cfquery>
<cfdump var="#GetResultA#"> CFM<br />

<cfscript>
   GetResultB="";
   GetSQL=New Query();
   GetSQL.setDatasource("TestData");
   GetSQL.setName("GetTestB");
   GetSQL.setResult("GetResultB");
   GetSQL.setSQL("#TestSQL#");
   GetResultB=GetSQL.execute();
   WriteDump(GetResultB.getPrefix());
</cfscript> CF9 CFSCRIPT<br />

The results are for a ColdFusion 9 server running Microsoft Server 2008 R2 communicating through a crossover to another Microsoft Server 2008 R2 with Microsoft SQL 2008. The execution times were not consistent, but the variance between the three query configurations was reasonably similar. Running the tests several times then averaging the results seemed to provide a clearer difference. The surprise was how much faster a CFC query actually runs, most of the time it was almost the same as the CFSTOREDPROC.

Average speeds:

CFM 972

CFC 728

SP 629

I was expecting the stored procedure to be the clear winner, especially with 58,960 record results but was impressed by the CFSCRIPT query speeds. Your results may vary.

If you find this post useful please leave a comment and let me know how you used the information.

Here is an example of how to use CASE statements in MS SQL to evaluate the size of a number (in this case a file size recorded in a database). Since the file size is numeric in this example, we are going to CAST it into a VARCHAR while we append descriptors to the end, to show "bytes", "kb", and "mb" file size abbreviations. Additionally, we will be using LEFT to remove either 3 or 6 character places from the right side of our file size number.

SELECT   CASE
      WHEN   FileSize < 1000
      THEN   CAST(FileSize AS VARCHAR) + ' bytes'
      WHEN   FileSize >= 1000 AND FileSize < 999999
      THEN   CAST(Left(FileSize, Len(FileSize)-3) AS VARCHAR) + ' kb'
      WHEN   FileSize >= 1000000
      THEN   CAST(Left(FileSize, Len(FileSize)-6) AS VARCHAR) + ' mb'
   END AS FileSize
FROM   SampleTable

If this method is used in a stored procedure, for use with ColdFusion, then we've taken some of the work performed by our application server and given to the SQL server instead. You could just record the abbreviated file sizes and their notations in the database, but you may end up saving a little space in database size storing the numerical representation. Plus, the upload results tend to be calculations in whole numbers, not mixed text.

The CASE statement may not be the best choice or right for every method and occasion, but in configurations where the application server and the SQL server are equally configured (reasonably); making MS SQL do more can help speed up application server processes.

If you find this post useful please leave a comment and let me know how you used the information.