Combining multiple SQL queries for listing pages into one

Unfortunately, I’m often tasked with maintaining other peoples code. This isn’t always a bad thing though, as you can really learn from other peoples coding practices.

Occasionally though, you see something that is elegant as far as code organization/readability goes, but isn’t necessarily geared towards performance.

In some extreme cases, I’ve seen code that is extremely inefficient. One such case is that of looping over database queries.

This practice involves wrapping a query in a for loop, which changes one condition within the query with each iteration. I’ve seen this most commonly on listing pages.

For example, if you were to create a product listing page with pricing, the inefficient “looped query” method would look something like this (using ColdFusion code for my examples):

<cfloop list="#productList#" index="product">
  <cfquery name="modelQuery">
     SELECT code, price
     FROM models
     WHERE code = <cfqueryparam value="#product#">
  </cfquery>

  <cfoutput>#product#: #modelQuery.price#
</cfloop>

Let’s think about this though. With each iteration, you are calling an external program (the database server) to make a request. Now consider that your database might be on another server and the inefficiency compounds.

With a little extra effort, you can construct a single query that returns multiple rows using the SQL IN operator.

From there, you can convert the query results into a language construct that is easy to work with. In ColdFusion, this would be a structure, in PHP, an associative array.

<cfset allModels = "prod1,prod2,prod3,prod4">
<cfset modelCount = ListLen(allModels)>

<cfquery name="modelsQuery">
  SELECT name, price
  FROM models
  WHERE code IN (
    <cfloop from="1" to="#modelCount#" index="i">
        <cfqueryparam value="#ListGetAt(allModels, i)#">
        <cfif i neq modelCount>, </cfif>
    </cfloop>)
</cfquery>

<cfset modelStruct = StructNew()>
<cfloop query="modelsQuery">
    <cfset modelStruct[modelsQuery.code] = modelsQuery.price>
</cfloop>

A dump of the resulting structure might look like this:

From here, you can simply loop over your list of products, and request a value from the structure with each iteration.

<cfloop list="#productList#" index="product">
  <cfoutput>#product#: #modelStruct[product]#
</cfloop>

As you can see, the code is a little bit more lenghty, and perhaps less elegant. But, it saves multiple trips to the database, which will speed up your page load.