How do we compare two Query result sets in coldfusion

Question

I need to build a generic method in coldfusion to compare two query result sets... Any Ideas???

Answer 1

If I understand you correctly, you have two result sets with same structure but different datasets (like selecting with different clauses).

If this is correct, I believe that better (more efficient) way is to try to solve this task on the database level. Maybe with temporarily/cumulative tables and/or stored procedure.

Using CF it is almost definitely will need a ton of loops, which can be inappropriate for the large datasets. Though I did something like this for the small datasets using intermediate storage: converted one result set into the structure, and looped over the second query with checking the structure keys.

Answer 2

If you are looking to simply decide whether two queries are exactly alike, then you can do this:

if(serializeJSON(query1) eq serializeJSON(query2)) ...

This will convert both queries to strings and compare the strings.

If you're looking for more nuance, I believe Sergii's approach (convert to struct, compare keys) is probably the right approach. You could "guard" it by adding in simple checks first.... do the column lists match? Is the recordcount the same? That way, if either of those checks fail, you know that the queries can't possibly be equivalent and so it's safe to return false, thereby avoiding the performance hit of a full compare.