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
WHERE 1 = 1
<cfif isDefined(‘attributes.startDate’) AND trim(attributes.startDate) is not ”>
AND search_log.queryDate >= #createODBCDate(attributes.startDate)#
<cfif isDefined(‘attributes.endDate’) AND trim(attributes.endDate) is not ”>
AND search_log.queryDate <= #createODBCDate(attributes.endDate)#
GROUP BY search_log.keywords
ORDER BY hitCount DESC
And the page went from taking about 30 seconds to run to instantly loading.