I’ve been running through some online courses concerning Oracle systems. One of the earlier lessons discusses performing arithmetic with your SQL statement so that not just the data in the table is returned, but manipulated table data is returned as well. This peaked my interest since it’s so simple, and yet I’ve never really considered using SQL to do basic arithmetic work from within the SQL statement as opposed to just manipulating the base values with Coldfusion.
Just out of curiosity I ran some tests on my local system, which of course doesn’t run Oracle, but does run MySql. The test consists of the idea that a company has a database of job applications, but after 30 days they want them to be marked as expired. So essentially what I want to end up with is an application date plus 30 days, and then compare if that date is greater than today’s date. All we want to potentially use the database for is adding the 30 additional days to each date in the database. Because I didn’t have any data to work with I used the following function to enter 500 dummy records into MySql.
<cffunction name="buildQuery" displayname="Build Query" hint="Needed to build a simple set of tables with applications and creation dates." access="public" output="false" returntype="void">
<cfoutput>
<cfloop from="1" to="500" index="i">
<cfset var startDate = dateFormat(now()) />
<cfset var endDate = dateFormat(now() - 365) />
<cfset var randomDate = randRange(startDate, endDate) />
<cfquery name="insertApps" datasource="apps">
INSERT INTO application_tbl (application_created)
VALUES (#randomDate#)
</cfquery>
</cfloop>
</cfoutput>
</cffunction>
This function simply takes a random date between now and one year ago and inserts it into the application_tbl table. Now we have some data to process with Coldfusion and SQL. The two functions that return the same thing, but one use CF and the other SQL are.
<!--- Process with Coldfusion --->
<cffunction name="processWithCF" displayname="Process with Coldfusion" hint="Processes the expiration date with Coldfusion" access="public" output="false" returntype="array">
<cfset var appsQuery = getQuery() />
<cfset var appsAry = arrayNew(1) />
<cfset var finalDate = "" />
<cfloop query="appsQuery">
<cfset finalDate = application_created + 30 />
<cfset arrayAppend(appsAry, dateFormat(finalDate, "full")) />
</cfloop>
<cfreturn appsAry />
</cffunction>
<cffunction name="getQuery" displayname="Get Query" hint="Gets a query full of applications and dates created." access="private" output="false" returntype="query">
<cfset var thisQuery = "" />
<cfquery name="thisQuery" datasource="apps">
SELECT *
FROM application_tbl
</cfquery>
<cfreturn thisQuery />
</cffunction>
<!--- Process with SQL --->>
<cffunction name="processWithSQL" displayname="Process with SQL" hint="Processes the expiration date with SQL" access="public" output="false" returntype="array">
<cfset var appsQuery = getQueryProcessed() />
<cfset var appsAry = arrayNew(1) />
<cfloop query="appsQuery">
<cfset arrayAppend(appsAry, dateFormat(application_created, "full"))>
</cfloop>
<cfreturn appsAry />
</cffunction>
<cffunction name="getQueryProcessed" displayname="Get Query" hint="Gets a query full of applications and dates created." access="private" output="false" returntype="query">
<cfset var thisQuery = "" />
<cfquery name="thisQuery" datasource="apps">
SELECT application_id, application_created + 30 AS application_created
FROM application_tbl
</cfquery>
<cfreturn thisQuery />
</cffunction>
The only difference between the two is that with the function getQueryProcessed() the SQL statement returns ‘application_created + 30 AS application_created’. This is returning a date created 30 days later than the actual date created. In the getQuery() function the statement is returning the raw data, but I am then using Coldfusion ‘finalDate = application_created + 30′ to set the date to 30 days in advance of the actual date.
In order to time the processing of both simple formats I use the following index page which outputs the average processing time for both according to 1000 iterations over both methods. The index page that monitors performance is the following.
<!--- set of lists to keep up with each time of processing --->
<cfset variables.cfProcessingList = "" />
<cfset variables.sqlProcessingList = "" />
<cfset VARIABLES.process = createObject("component", "processExpiration").init() />
<cfset variables.startCount = getTickCount() />
<cfloop from="1" to="1000" index="variables.i">
<cfset VARIABLES.processedWithCF = VARIABLES.process.processWithCF() />
</cfloop>
<cfset variables.endCount = getTickCount() />
<!--- time to process and output instance --->
<cfset variables.processingTime = variables.endCount - variables.startCount />
<!--- append processing time to list --->
<cfset variables.cfProcessingList = listAppend(variables.cfProcessingList, variables.processingTime, ",") />
<cfset variables.startCount = getTickCount() />
<cfloop from="1" to="1000" index="variables.i">
<cfset VARIABLES.processedWithSQL = VARIABLES.process.processWithSQL() />
</cfloop>
<cfset variables.endCount = getTickCount() />
<!--- time to process and output instance --->
<cfset variables.processingTime = variables.endCount - variables.startCount />
<!--- append processing time to list --->
<cfset variables.sqlProcessingList = listAppend(variables.sqlProcessingList, variables.processingTime, ",") />
<!--- average processing times --->
<cfset variables.cfProcessingAvg = arrayAvg(listToArray(variables.cfProcessingList, ",")) />
<cfset variables.sqlProcessingAvg = arrayAvg(listToArray(variables.sqlProcessingList, ",")) />
<cfoutput>
<br /><br /><br />
CF Processing Average: #variables.cfProcessingAvg#<br /><br />
SQL Processing Average: #variables.sqlProcessingAvg#
</cfoutput>
The results:
Average Coldfusion processing time – 23509 ms
Average SQL processing time – 34163 ms
Considering that this was 1000 iterations we’re talking marginal differences at best. As such, when I see two ways of performing the same process that takes roughly the same amount of time to process, I immediately begin looking at design. This is an instance where I do feel the context in which this type of performing is needed should determine the method that is used. Nonetheless, with my experience I think the majority of the time using Coldfusion to perform the data manipulation portion is best practice.
I say this because what does take time are database connections and the process of calling data from a database. Let’s take our theoretical example above and say that we want to not only see which applications are being thrown away, but also those that would be thrown away if our cutoff was 60 days old and 90 days old. To do this the really bad way, we would write three different SQL statements, each adding 30, 60, and 90 days to each returned result. A much better way of doing it would be to return each set within the same SQL statement so that we are only making one database call. This statement would look like:
SELECT application_id, application_created + 30 AS application_created_thirty, application_created + 60 AS application_created_sixty, application_created + 90 AS application_created_ninety
FROM application_tbl
Even better and more flexible is to simply return the data with SQL and then use Coldfusion to do all data manipulation. It will simply run faster and be more flexible to change easily over time. An abstract function can be written that adds a passed in number to a passed in value and returns the result. This doesn’t restrict us to statements. The abstract function could look like the following.
<cffunction name="addStuff" displayname="Add Stuff" hint="Adds any passed in number to any passed in value and returns it." access="public" output="false" returntype="Numeric">
<cfargument name="value" displayname="Value" hint="Value to add to." type="numeric" required="true" />
<cfargument name="toBeAdded" displayname="To Be Added" hint="Number to be added to value." type="numeric" required="true" />
<cfset var result = ARGUMENTS.value + ARGUMENTS.toBeAdded />
<cfreturn result />
</cffunction>
Now after returning the raw data from the database the above function can be used for manipulation as such.
<cfset VARIABLES.result1 = addStuff(application_created, 30) />
<cfset VARIABLES.result2 = addStuff(application_created, 60) />
<cfset VARIABLES.result3 = addStuff(application_created, 90) />
The beauty of this is that we can continue to add to it simply and quickly. Let’s say our boss came to us and wanted to see how many applications would be left under 45 and 50 days old. We would just add to our code:
<cfset VARIABLES.result4 = addStuff(application_created, 45) />
<cfset VARIABLES.result5 = addStuff(application_created, 50) />
So this is how I see it. There are probably situations where it would make sense to process arithmetic with SQL, but for the most part I feel performing data manipulation with Coldfusion is the way to go. Nonetheless, having the ability to do these things with SQL alone is certainly a powerful tool to have in one’s arsenal.
At Monserrate Monastery in Bogotá, Colombia.