Thought Delimited

Every ColdFusion developer who spends a lot of time working with queries knows that if you have to run a unique query for every iteration of a loop, you should (whenever possible) use a <cfquery> to acquire the entire set of records prior to the loop and then query against that recordset using a Query of Queries (QofQ):

<cfquery name="qryMain" datasource="myDatabase">
    select a.field1, a.field2, b.field7 ...
    from table1 a, table2 b
    where a.field1= b.field2
</cfquery>

<cfloop index="j" from="1" to="1000">
    ....
    <cfset calculatedVal= thisVal + thatVal />
    
    <cfquery name="qrySub" dbtype="query">
        select field2, field7 ...
        from qryMain
        where field1= <cfqueryparam value="#calculatedVal#" cfsqltype="cf_sql_numeric" />
    </cfquery>
    ...
</cfloop>

...That way, you're querying the recordset in memory 1,000 times instead of making 1,000 calls to the database.

Yesterday I was asked to look at an old report that wasn't performing well anymore now that the database tables being queried had grown much larger. While it was designed to use QofQ data within the loop, the QofQ in the loop was doing a join between two recordsets stored in memory:

<cfquery name="dbQry1" datasource="myDatabase">
    select a.field1, a.field2, a.field3, b.field4, b.field5
    from table1 a, table2 b
    where a.field1= b.field2
    ...
</cfquery>

<cfquery name="dbQry2" datasource="myDatabase">
    select c.field1, c.field6, d.field9
    from table3 c, table4 d
    where c.field1= d.field3
    ...
</cfquery>

<cfloop index="j" from="1" to="1000">
    ....
    <cfset calculatedVal= thisVal + thatVal />
    
    <cfquery name="qrySub" dbtype="query">
        select dbQry1.field2, dbQry1.field3, dbQry2.field6, dbQry2.field9
        from dbQry1, dbQry2
        where dbQry1.field1= dbQry2.field1
        and dbQry.field2 > <cfqueryparam value="#calculatedVal#" cfsqltype="cf_sql_numeric" />
        ...
    </cfquery>
    ...
</cfloop>

I discovered that if I created a third QofQ that took care of the join outside of the loop, and queried that new recordset within the loop, the per-iteration performance improved dramatically:

<cfquery name="dbQry1" datasource="myDatabase">
    select a.field1, a.field2, a.field3, b.field4, b.field5
    from table1 a, table2 b
    where a.field1= b.field2
    ...
</cfquery>

<cfquery name="dbQry2" datasource="myDatabase">
    select c.field1, c.field6, d.field9
    from table3 c, table4 d
    where c.field1= d.field3
    ...
</cfquery>

<cfquery name="qryMasterSub" dbtype="query">
    select dbQry1.field2, dbQry1.field3, dbQry2.field6, dbQry2.field9
    from dbQry1, dbQry2
    where dbQry1.field1= dbQry2.field1
</cfquery>


<cfloop index="j" from="1" to="1000">
    ....
    <cfset calculatedVal= thisVal + thatVal />
    
    <cfquery name="qrySub" dbtype="query">
        select field2, field3, field6, field9
        from qryMasterSub
        where field2 > <cfqueryparam value="#calculatedVal#" cfsqltype="cf_sql_numeric" />
        ...
    </cfquery>
    ...
</cfloop>

It makes sense when you think about it, but I had just never considered that join operations would affect QofQs so drastically.