Tweets for Today
Reply
Author Archives: Michael C. McGreevy
Saturday, June 21st – Television Preview
I have four tickets to a Television Preview in Clearwater that I cannot use because I have friends coming over to hang out. If anyone would like them they are welcome to them.
Just as a heads up – This isn’t a scam of any kind. The reason I got the tickets is because I’ve been to one before, and it’s everything they advertise it to be. They’ll show you a few half hour shows, get your feedback, and give away some door prizes. I can’t remember if they actually bothered to give out refreshments or not, but I think they did (it’s been a while). The shows I saw when I went were pretty awful and none of them made it on the air, so I can’t make any promises that you’ll actually be ENTERTAINED (unless, that is, you like bad television). They also won’t bug you a lot if you give them your contact information. This is the first contact I’ve had from them since the last event I went to and that was over 12 years ago.
Cross posted to tampa
Eddie Izzard
Well…that was kinda…meh.
Funny at points, but not the laugh riot I’ve come to expect. I suspect a lot of it had to do with the fact that he was exhausted. His energy seemed really low, and he mentioned not being able to do a sound check due to some kind of hectic scheduling issues. Which, in all honesty, showed. The sound levels were not right. Apparently the folks in the balcony and further back could hear him fine, because they were laughing at jokes that we (in the third row) couldn’t understand. I dunno. Hopefully he’ll be better tomorrow night for those of you who are going to see him, but overall…I just didn’t laugh that much.
If you’ve ever had me in one of your audiences, you know that’s not a very common thing.
Update
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.
Tweets for Today
Coding/SQL question…
Ok, folks….This one is going out to all of you who work in SQL, cold fusion, etc…
I am currently working on series of reports based on keyword search data that we have archived from our main web site. The page I’m working on is supposed to display the most popular keywords in descending order. My initial thought was that I’d be able to get the data with one SQL query like so…
SELECT Count(search_log.keyword) AS hitCount, search_log.keyword
FROM search_log
WHERE search_log.keyword IN (SELECT DISTINCT search_log.keyword FROM search_log)
The problem is that once I throw COUNT in the mix SQL doesn’t seem to like returning any additional data from the table, so I had to break it down and find the results with cold fusion. Doing so requires me to run a huge series of queries to get all of my data and the page takes forever to load. Here’s what I’m doing…
<cfquery name=”getLog” datasource=”#request.datasource#”>
SELECT DISTINCT (search_log.keywords)
FROM search_log
</cfquery><cfset keywordCountArray = ArrayNew(1)>
<cfloop query=”getLog”>
<cfquery name=”getCount” datasource=”#request.datasource#”>
SELECT COUNT(search_log.keywords) AS hitCount
FROM search_log
WHERE search_log.keywords = ‘#getLog.keywords#’
</cfquery>
<cfset keywordCountArray[getLog.currentRow] = getCount.hitCount>
</cfloop><cfset newColumn = queryAddColumn(getLog, ‘hitCount’, ‘Integer’, keywordCountArray)>
<cfquery name=”getLogData” dbtype=”query”>
SELECT getLog.keywords, getLog.hitCount
FROM getLog
ORDER BY getLog.hitCount DESC
</cfquery>
My thoughts on how to improve this…
I could just cache the “getLogData” query and not run the rest of them if the query exists, but I’m not sure if I can check for the existence of a cached query in ColdFusion.
Do the logic in a stored procedure. Would speed things up slightly but, I think, still require me to run a multitude of queries.
Re-write the SQL in some terribly simplistic way that I’m just not seeing because I’m being blind.
Any help out there, gang?
Tweets for Today
Tweets for Today
A word to the wise…
Changing a tire is much easier when you realize you have a tire jack in the other one.
Socket wrenches? Not so much.
Oh, my poor hands.