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.
Launch code in new window » Download code as text file »
Launch code in new window » Download code as text file »
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
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