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.
Recent Comments