ColdFusion QueryAppend( qOne, qTwo )
Posted July 5, 2006 at 9:26 AM
Someone was asking me about a QueryAppend() method in ColdFusion. So this morning, I finally got around to writing a snippet demo about it. The way I figured it out, there are 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
Launch code in new window » Download code as text file »
- <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.">
- <!--- Define arguments. --->
- <cfargument name="QueryOne" type="query" required="true" />
- <cfargument name="QueryTwo" type="query" required="true" />
- <cfargument name="UnionAll" type="boolean" required="false" default="true" />
- <!--- Define the local scope. --->
- <cfset var LOCAL = StructNew() />
- <!--- Append the second to the first. Do this by unioning the two queries. --->
- <cfquery name="LOCAL.NewQuery" dbtype="query">
- <!--- Select all from the first query. --->
- (
- SELECT
- *
- FROM
- ARGUMENTS.QueryOne
- )
- <!--- Union the two queries together. --->
- 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 all from the second query. --->
- (
- SELECT
- *
- FROM
- ARGUMENTS.QueryTwo
- )
- </cfquery>
- <!--- Return the new query. --->
- <cfreturn LOCAL.NewQuery />
- </cffunction>
Method Two: Manually Adding Rows
Launch code in new window » Download code as text file »
- <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.">
- <!--- Define arguments. --->
- <cfargument name="QueryOne" type="query" required="true" />
- <cfargument name="QueryTwo" type="query" required="true" />
- <!--- Define the local scope. --->
- <cfset var LOCAL = StructNew() />
- <!--- Get the column list (as an array for faster access. --->
- <cfset LOCAL.Columns = ListToArray( ARGUMENTS.QueryTwo.ColumnList ) />
- <!--- Loop over the second query. --->
- <cfloop query="ARGUMENTS.QueryTwo">
- <!--- Add a row to the first query. --->
- <cfset QueryAddRow( ARGUMENTS.QueryOne ) />
- <!--- Loop over the columns. --->
- <cfloop index="LOCAL.Column" from="1" to="#ArrayLen( LOCAL.Columns )#" step="1">
- <!--- Get the column name for easy access. --->
- <cfset LOCAL.ColumnName = LOCAL.Columns[ LOCAL.Column ] />
- <!--- Set the column value in the newly created row. --->
- <cfset ARGUMENTS.QueryOne[ LOCAL.ColumnName ][ ARGUMENTS.QueryOne.RecordCount ] = ARGUMENTS.QueryTwo[ LOCAL.ColumnName ][ ARGUMENTS.QueryTwo.CurrentRow ] />
- </cfloop>
- </cfloop>
- <!--- Return out. --->
- <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.
Download Code Snippet ZIP File
Comments (2) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Reader Comments
I'm trying your method number 2 but CF doesn't like it. Gives me this error on the line where you "set the column value on the newly created row":
An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.
Posted by William Haun on Jun 10, 2007 at 6:56 PM
@William,
That ColdFusion error usually comes about when you misspell one of the column names. If that happens, then ColdFusion thinks you are trying to add a new column rather than updating an existing old one.
In this case, since you are adding one query to the other, this would probably come about if the two queries do not have the exactly same column names.
Posted by Ben Nadel on Jun 10, 2007 at 10:19 PM
