ColdFusion QueryAppend( qOne, qTwo )

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

Method Two: Manually Adding Rows

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.

What Other People Are Searching For

[ local search ] append query coldfusion
[ local search ] query append coldfusion
[ local search ] how to add append to a record in coldfusion
[ local search ] add a query to another second query in coldfusion
[ local search ] cfscript query add row
[ local search ] coldfusion add query results together
[ local search ] adding queries as rows coldfusion
[ local search ] appending queries coldfusion
[ local search ] order of execution of sql clauses
[ local search ] coldfusion query structure append
[ local search ] coldfusion adding queries
[ local search ] adding one query to another coldfusion
[ local search ] coldfusion add query row