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.<\/p>\n
Occasionally though, you see something that is elegant as far as code organization\/readability goes, but isn’t necessarily geared towards performance.<\/p>\n
In some extreme cases, I’ve seen code that is extremely inefficient. One such case is that of looping over database queries.<\/p>\n
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.<\/p>\n
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):<\/p>\n
<cfloop list=\"#productList#\" index=\"product\">\r\n <cfquery name=\"modelQuery\">\r\n SELECT code, price\r\n FROM models\r\n WHERE code = <cfqueryparam value=\"#product#\">\r\n <\/cfquery>\r\n\r\n <cfoutput>#product#: #modelQuery.price#\r\n<\/cfloop><\/pre>\nLet’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.<\/p>\n
With a little extra effort, you can construct a single query that returns multiple rows using the SQL IN operator.<\/p>\n
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.<\/p>\n
<cfset allModels = \"prod1,prod2,prod3,prod4\">\r\n<cfset modelCount = ListLen(allModels)>\r\n\r\n<cfquery name=\"modelsQuery\">\r\n SELECT name, price\r\n FROM models\r\n WHERE code IN (\r\n <cfloop from=\"1\" to=\"#modelCount#\" index=\"i\">\r\n <cfqueryparam value=\"#ListGetAt(allModels, i)#\">\r\n <cfif i neq modelCount>, <\/cfif>\r\n <\/cfloop>)\r\n<\/cfquery>\r\n\r\n<cfset modelStruct = StructNew()>\r\n<cfloop query=\"modelsQuery\">\r\n <cfset modelStruct[modelsQuery.code] = modelsQuery.price>\r\n<\/cfloop><\/pre>\nA dump of the resulting structure might look like this:<\/p>\n
struct<\/span><\/th>\n<\/tr>\n| prod1<\/span><\/td>\n | 19.95<\/span><\/td>\n<\/tr>\n | prod2<\/span><\/td>\n | 49.95<\/span><\/td>\n<\/tr>\n | prod3<\/span><\/td>\n | 49.95<\/span><\/td>\n<\/tr>\n | prod4<\/span><\/td>\n | 39.95<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n | From here, you can simply loop over your list of products, and request a value from the structure with each iteration.<\/p>\n <cfloop list=\"#productList#\" index=\"product\">\r\n <cfoutput>#product#: #modelStruct[product]#\r\n<\/cfloop><\/pre>\n |
|---|