如何使用Query of Queries优化ColdFusion代码执行效率？

Your hunch is spot-on—running repeated database queries inside a loop is a classic performance killer. Each iteration adds a round-trip to the database, which gets exponentially slow as your get_equipment result set grows. Let's break down how to refactor this using QoQ and batch operations to cut down on database chatter.

Step 1: Fetch All Required Data in Bulk First #

Instead of querying related tables inside the loop, pull all the data you need in a few initial queries. This reduces database round-trips from N+1 to just 4 total (plus your main equipment query).

First, clean up and parameterize your main equipment query:

<!--- Get main equipment list (cleaned up and secure) --->
<cfquery name="get_equipment" datasource="#datasource#">
    SELECT * 
    FROM equipment_maintenance 
    WHERE machine_type NOT IN ('unifi_site', 'firewall', 'dvr', 'pbx')
      AND active = 'yes' 
    ORDER BY <cfqueryparam value="#querySortType#" cfsqltype="CF_SQL_VARCHAR">
</cfquery>

Then fetch all related data in bulk, filtering only for the equipment IDs you care about:

<!--- Get ALL in-progress service tickets for our equipment --->
<cfquery name="all_in_progress_history" datasource="#datasource#">
    SELECT * 
    FROM service_ticket 
    WHERE equipment_id IN (
        <cfqueryparam value="#get_equipment.id#" list="true" cfsqltype="CF_SQL_INTEGER">
    )
</cfquery>

<!--- Get ALL service history records --->
<cfquery name="all_equipment_history" datasource="#datasource#">
    SELECT * 
    FROM equipment_service_history 
    WHERE equipment_id IN (
        <cfqueryparam value="#get_equipment.id#" list="true" cfsqltype="CF_SQL_INTEGER">
    )
</cfquery>

<!--- Get ALL closed tickets (sorted upfront to avoid re-sorting later) --->
<cfquery name="all_closed_tickets" datasource="#datasource#">
    SELECT * 
    FROM closed_tickets 
    WHERE equipment_id IN (
        <cfqueryparam value="#get_equipment.id#" list="true" cfsqltype="CF_SQL_INTEGER">
    )
    ORDER BY ticket_id DESC
</cfquery>

Step 2: Use Query of Queries to Filter Data in Memory #

Now that you have all data loaded into memory, use QoQ to filter records for each equipment item without hitting the database again. QoQ runs directly on your ColdFusion server's memory, which is far faster than repeated database calls.

<cfoutput query="get_equipment">
    <!--- QoQ to get in-progress history for this specific equipment --->
    <cfquery name="get_in_progress_history" dbtype="query">
        SELECT * 
        FROM all_in_progress_history 
        WHERE equipment_id = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER">
    </cfquery>

    <!--- Output your in-progress data here --->
    ...

    <!--- QoQ to get service history for this equipment --->
    <cfquery name="get_history" dbtype="query">
        SELECT * 
        FROM all_equipment_history 
        WHERE equipment_id = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER">
    </cfquery>

    <!--- QoQ to get closed tickets for this equipment --->
    <cfquery name="get_history_ticket_detail" dbtype="query">
        SELECT * 
        FROM all_closed_tickets 
        WHERE equipment_id = <cfqueryparam value="#id#" cfsqltype="CF_SQL_INTEGER">
    </cfquery>

    <!--- Output your history data here --->
    ...

    <cfloop query="get_history_ticket_detail">
        <!--- Output closed ticket details here --->
        ...
    </cfloop>
</cfoutput>

Step 3: Batch Your Update Operations #

The original loop runs an UPDATE for each equipment item—this is another major performance drain. Instead, collect all the update data first, then run a single batch update.

Option 1: Use a CASE WHEN Statement (Most Databases Support This) #

This method updates all records in one database call:

<!--- First, collect all update values in an array --->
<cfset updateQueue = []>
<cfoutput query="get_equipment">
    <!--- Calculate your dueDate value here as you did originally --->
    <cfset arrayAppend(updateQueue, {id=id, dueDate=dueDate})>
</cfoutput>

<!--- Run the batch update if there are records to modify --->
<cfif arrayLen(updateQueue) > 0>
    <cfquery name="batchUpdateDueDates" datasource="#datasource#">
        UPDATE equipment_maintenance 
        SET maintenance_due_date = CASE 
            <cfloop array="#updateQueue#" index="item">
                WHEN id = <cfqueryparam value="#item.id#" cfsqltype="CF_SQL_INTEGER"> 
                THEN <cfqueryparam value="#item.dueDate#" cfsqltype="CF_SQL_DATE">
            </cfloop>
            ELSE maintenance_due_date
        END
        WHERE id IN (
            <cfqueryparam value="#arrayToList(updateQueue.id)#" list="true" cfsqltype="CF_SQL_INTEGER">
        )
    </cfquery>
</cfif>

Bonus Performance Tips #

• Avoid SELECT *: Only fetch the columns you actually need instead of all columns—this reduces memory usage and query execution time.
• Index Your Database: Ensure equipment_id in service_ticket, equipment_service_history, and closed_tickets is indexed. Also add indexes to machine_type and active in equipment_maintenance to speed up your initial query.
• Parameterize Everything: We added <cfqueryparam> to all queries—this prevents SQL injection and helps your database cache execution plans for faster repeated queries.

内容的提问来源于stack exchange，提问作者Brian Fleishman