search performance (queries of queries and queries into structures)

Just a question about Michael's interesting post about the relative performance of QofQ's vs. turning queries into lists, structures, etc. and cacheing them... It looks from the post that you're talking about doing searches on single columns of data. What about matching 2 columns? Example: I have a list of IPs that are banned from certain areas of the site. Obviously this needs to be cached to be checked on every request. Currently I'm caching a query that gets all banned IP infos, then do a QofQ to see if current IP is banned from current area. Would the "fastest" solution proposed - turning query into structure, cacheing structure (presumably in application scope) and searching structure - still work for this type of thing. I can't think offhand how you'd do the equivalent of WHERE area = '#request.area#' with a structure of structures, but I guess it's possible. Does it still measure up against QofQ's even though you're not just doing a simple search for one value with StructKeyExists()? cheers, Gyrus gyrus@norlonto.net play: http://norlonto.net/ work: http://tengai.co.uk/ PGP key available

You'd almost certainly see nice a performance improvement by making a set of structures for site areas where the IP address is the key, and the value is a structure of the other data in the record, or even the empty string: <cfset application.bannedIPs = structNew() /> <cfoutput query="myIpList" group="sitearea">   <cfset application.bannedIPs[sitearea] = structNew() />   <cfoutput>     <cfset application.bannedIPs[sitearea][ip] = "" />   </cfoutput> </cfoutput> Searching that with structKeyExists() should be much faster than doing a QofQ on a recordset: <cfif structKeyExists(application.bannedIPs[sitearea], cgi.remote_addr)>   <cfabort showerror="you are not allowed" /> </cfif> You could also do it this way: <cfset application.bannedIPs = structNew() /> <cfoutput query="myIpList">   <cfif structKeyExists(application.bannedIPs, ip)>     <cfset application.bannedIPs[ip] = listAppend(application.bannedIPs[ip], lcase(sitearea)) />   <cfelse>     <cfset application.bannedIPs[ip] = lcase(sitearea) />   </cfif> </cfoutput> and <cfif structKeyExists(application.bannedIPs, cgi.remote_addr)   AND listFind(application.bannedIPs[cgi.remote_addr], lcase(sitarea))>     <cfabort showerror="you are not allowed" /> </cfif> The former would probably be faster, but you'd have to test to be sure. Of course, this is a purpose-built solution, but that's usually warranted if you have very performance-sensitive code.  The more so if the solution isn't complex.  And if that solution isn't fast enough, you can skip the CF structs, and use native Java Hashmaps or HashSets directly (or the Jakarta FastHashMap, if you want).  I have observed them to be faster, because you avoid the overhead of the CF wrapping around them. HTH, barneyb --- Barney Boisvert, Senior Development Engineer AudienceCentral bboisvert@audiencecentral.com voice : 360.756.8080 x12 fax   : 360.647.5351 www.audiencecentral.com ----- Excess quoted text cut - see Original Post for more -----