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.