Uber-query clarification.

Sun, 17 Sep 2006 12:55:00 -0400

I just got finished listening to this week ColdFusion Weekly podcast. I was flattered to hear that this post was discussed. However, I would like to make some clarifications on my post, and what was discussed.

First, like Matt (I think it was Matt, its hard for me to tell them apart), I prefer to have the databse do as much work as possible. That was not really possible in the situation I was referring to in my post. In the situation I described, I was inserting or updating data, as much as 10,000 rows at a time. I was NOT retrieving data from the database, looping over the results of that query and performing other queries (typically, that's what JOINs are for).

As I pointed out, I am unsure of whether or not the multiple queries was faster due to the use of <cfqueryparam> or not. I also mentioned that I was going to test to see what would happen if i removed the <cfqueryparam> from the multiple queries. Unfortuantely, I am no longer stationed on-site at that client any longer, so that part of the process shall remain a mystery.

Let me say again, I prefer to let the database do as much work as possible. However, when inserting or updating large sets of data, this may not be the best course of action, and as they guys pointed out, 'it depends'.

Thanx Matt and Peter, keep up the good work.

SUM() cool stuff

Thu, 14 Sep 2006 13:39:00 -0400

One of my personal projects is an on-line football pool that is run by a fried of mine. The pool is fairly simple, you pick 2 teams each week who you think will have the highest margin of victory (MOV), the person who has the highest aggregate MOV at the end of the season wins.

I was working on a 'stats' page yesterday to show information about the teams chosen by the entrants, as well as MOV information based on team, conference, division, etc... and ran into a potential issue with my database structure.

I have a 'game' table, which has the following columns: gameID, SeasonID, week, homeTeamID, awayTeamID, homeScore, awayScore. The potential issue was that for any given week, a team could be in the awayTeamID column, or the homeTeamID column. It was easy enough to determine this by using a CASE...WHEN statement in my SQL.
[code]...
CASE WHEN team.teamID = awayTeamID THEN stuff for away team
ELSE some stuff for home team[/code]

There was one problem with this, one of the 'reports' I was working on required the season-long MOV for every team, which means I needed to use SUM(). I know I could have handled this in CF, but I prefer to let the database do what it is good at, and try to avoid unnecessary processing of data with CF.

On a whim, I decided to try something rather odd, and surprisingly, it worked. I tried putting the case statement inside the SUM() call, like this.
[code]...
SUM(
    CASE WHEN t.teamid = gh.homeTeamID THEN gh.homeScore-gh.awayScore
    ELSE gh.awayScore-gh.homeScore
    END
    ) AS MOV
...[/code]

Basically what this does is use the CASE statement to determine if the team is the home team, if so, it calculates the MOV as homeScore-awayScore. If the team is not the home team, they must be the away team, so the MOV is calculated as awayScore-homeScore.

I think its pretty cool that you can put logic inside of the SUM() call. I have not tested it, but I would imagine you could also add logic inside other aggregate functions like MAX() or MIN().

Boyzoid's Blog & Stuff - Database Stuff

Uber-query clarification.

SUM() cool stuff