So, I started poking around with creating a Stored Procedure this morning and I finally found the “you’re missing something insanely obvious” part of the equation.  All I needed to do to mix the aggregate function with a normal select was use the GROUP BY statement (which had the added bonus of also taking care of the need to use SELECT DISTINCT).

Long story short, I just reduced all of that code I posted yesterday to this.

<cfquery name=”getHitData” datasource=”#request.datasource#”>
    SELECT search_log.keywords, count(search_log.keywords) AS hitCount
    FROM search_log
    WHERE 1 = 1
    <cfif isDefined(‘attributes.startDate’) AND trim(attributes.startDate) is not ”>
        AND search_log.queryDate >= #createODBCDate(attributes.startDate)#
    </cfif>
    <cfif isDefined(‘attributes.endDate’) AND trim(attributes.endDate) is not ”>
        AND search_log.queryDate <= #createODBCDate(attributes.endDate)#
    </cfif>   
    GROUP BY search_log.keywords
    ORDER BY hitCount DESC
</cfquery>

And the page went from taking about 30 seconds to run to instantly loading.

Yay.