Nested Query Loops - Worst code ever

Posted At : May 25, 2006 10:44 PM | Posted By : Erik Voldengen
Related Categories: web

I had no idea how bad nested query loops are. But oh man, they are BAD.

I came across this "technique" this week when a very busy site started resonding extremely slowly - like 30 seconds per request, sometimes more. I didn't write the code, but I "got" to fix it.

Check this out, if you have a strong stomach:

<cfquery name="cities" datasource="whatever">
select CITY from TABLE ORDER BY CITY
</cfquery>

<cfquery name="places" datasource="whatever">
select CITY,PLACE from TABLE ORDER BY CITY,PLACE
</cfquery>

<cfoutput>
   <cfloop query="cities">
      #cities.CITY#:<br>
      <cfloop query="places">
         <cfif places.city IS cities.city>
         -#places.PLACE#<br>
         </cfif>
      </cfloop>
   </cfloop>
</cfoutput>

The above code runs amazingly slow!

I replaced it with something like this:

<cfquery name="places" datasource="whatever">
select CITY,PLACE from TABLE ORDER BY CITY,PLACE
</cfquery>

<cfoutput query="places" group="city">
   #places.CITY#:<br>
   <cfoutput group="place">
   -#places.PLACE#<br>
   </cfoutput>
</cfoutput>

This runs at least 10 times faster. Under heavy load (think scalability), it's about 100 times faster.

This is obviously a simplified example of what I encountered, but not by much. What if the nesting was three deep instead of two? With more complex code?

Like I said, worst...code...ever