How to Quickly Search a Query

Question

I have an application where almost everything is dynamic. I am creating an edit form for a user and essentially need to search a query to select a group of checkboxes.

I have a table assigning the user to programs that holds userid and programid which maps to the corresponding records in the users table and the programs table. Initially I grab one user and all the programs and I loop over the programs query to build the checkboxes.

<cfloop query="Rc.programs">
    <dd><input type="checkbox" name="programs" value="#Rc.programs.id#" /> #Rc.programs.name#</dd>
</cfloop>

What I ideally want to do is pull all records in the program memberships table and do some sort of search through that. I could do a query of queries, but I was wondering if there was a faster way to essentially search a query. My query of queries would be like the following if this helps people understand.

SELECT * FROM Rc.programs WHERE programid = #Rc.programs.id#

Answer 1

QoQ is certainly the easiest way to do it, but don't forget your CFQUERYPARAM:

SELECT * FROM Rc.programs WHERE programid =
   <cfqueryparam value="#Rc.programs.id#" cfsqltype="WHATEVER_IT_IS">

You can also reference an individual column/field of a query as an array, and search through just that column using array functions, including arrayFind() (which might just be in recent versions).

arrayFind( Rc.programs.programId, YOUR_ID_HERE )

If that's not fast enough you could always build some sort of data structure or index in memory, and keep it around in an Application-scope variable if such is appropriate.

But is your database really that slow? Reducing the number of queries executed by a page is almost always a good thing, but for average, uncomplicated queries you probably won't be able to beat the speed, caching, etc of your DB server.