Jun 172008

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 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.


Be Sociable, Share!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>