QueryAppend( qOne, qTwo ) : ColdFusion Snippets : Kinky Solutions : A Student's Perspective by Ben Nadel

QueryAppend( qOne, qTwo )

Snippets Overview

ColdFusion

DHTML

Flash

Javascript Library

Downloadable Files

In this demo we are exploring two different ways to append one query to another in ColdFusion. In the first method, QueryAppend( qOne, qTwo ), we are using the power of query of queries to union both queries together and return the resultant query. In the second method, QueryAppend2( qOne, qTwo ), we are looping through the second query and manually adding rows to the first query. As you can see from the results below, the second method (manually adding rows to the query) consistently performs much faster than the UNION ALL method.

Method One: Query of Queries

<cffunction name="QueryAppend" access="public" returntype="query" output="false"
hint="This takes two queries and appends the second one to the first one. Returns the resultant third query.">
<cfargument name="QueryOne" type="query" required="true" />
<cfargument name="QueryTwo" type="query" required="true" />
<cfargument name="UnionAll" type="boolean" required="false" default="true" />
<cfset var LOCAL = StructNew() />
<cfquery name="LOCAL.NewQuery" dbtype="query">
(
SELECT
*
FROM
ARGUMENTS.QueryOne
)
UNION
<!---
Check to see if we are going to care about duplicates. If we don't
expect duplicates then just union all.
--->
<cfif ARGUMENTS.UnionAll>
ALL
</cfif>
(
SELECT
*
FROM
ARGUMENTS.QueryTwo
)
</cfquery>
<cfreturn LOCAL.NewQuery />
</cffunction>

Method Two: Manually Adding Rows

<cffunction name="QueryAppend2" access="public" returntype="void" output="false"
hint="This takes two queries and appends the second one to the first one. This actually updates the first query and does not return anything.">
<cfargument name="QueryOne" type="query" required="true" />
<cfargument name="QueryTwo" type="query" required="true" />
<cfset var LOCAL = StructNew() />
<cfset LOCAL.Columns = ListToArray( ARGUMENTS.QueryTwo.ColumnList ) />
<cfloop query="ARGUMENTS.QueryTwo">
<cfset QueryAddRow( ARGUMENTS.QueryOne ) />
<cfloop index="LOCAL.Column" from="1" to="#ArrayLen( LOCAL.Columns )#" step="1">
<cfset LOCAL.ColumnName = LOCAL.Columns[ LOCAL.Column ] />
<cfset ARGUMENTS.QueryOne[ LOCAL.ColumnName ][ ARGUMENTS.QueryOne.RecordCount ] = ARGUMENTS.QueryTwo[ LOCAL.ColumnName ][ ARGUMENTS.QueryTwo.CurrentRow ] />
</cfloop>
</cfloop>
<cfreturn />
</cffunction>

Let's explore the pros and cons of the first method.

Cons: This method performs slower for several reasons. For one, it has to evaluate SQL statements. Even though this is on the ColdFusion side, it's still a good amount of overhead. Also, due to the fact that we are unioning we have to read every row from both the first query and the second query even though we are only appending the second query.

Pros: The only real pro to this method is that we can determine wether or not we get duplicate rows back via the ALL directive in the UNION clause. This is something that cannot be done efficiently in the second method (see below).

Let's explore the pros and cons of the second method.

Cons: The main con for this method is that we are not checking for any duplicate rows. We simply append the rows from the second query to the first.

Pros: The main pro here is that its wicked fast! Much faster in fact than the first method. This will always be the case. Additionally, this behaves more like the other Append methods in ColdFusion. In StructAppend() and ArrayAppend(), you don't get a returned result object. The first object passed in is altered by reference, and therefore, no result needs to be passed back. This method of the QueryAppend() works that way, by altering the first query directly, not needing to pass anything back.

Let's explore the speed difference, as the second method will ALWAYS be faster no matter how many rows we are dealing with. Think about a situation in which you have one query that has X number of rows and you want to union it with another query which has Y number of rows.

Method One: Reads in X + Y rows in the SELECT statements of the query. Then, it writes X + Y rows back into a resultant query. That's (2X) + (2Y) read/write operations (give or take).

Method Two: Reads in just from the second query Y rows. It then appends those Y rows to the first query with Y writes. That's (2Y) read/write operations(give or take). Therefore, the second method will always be a factor of (2X) faster than the first method since it does not care about the first query in any significant way.

Added July 5, 2006 / Updated July 5, 2006