Uber-query clarification.

Posted At : September 17, 2006 10:55 AM | Posted By : Scott Stroz
Related Categories: ColdFusion Stuff,Database Stuff

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.

Comments (0) |

Print |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 2575 Views

SUM() cool stuff

Posted At : September 14, 2006 11:39 AM | Posted By : Scott Stroz
Related Categories: ColdFusion Stuff,Database Stuff

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.

...CASE WHEN team.teamID = awayTeamID THEN stuff for away team
ELSE some stuff for home team

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.

...SUM(
    CASE WHEN t.teamid = gh.homeTeamID THEN gh.homeScore-gh.awayScore
    ELSE gh.awayScore-gh.homeScore
    END
    ) AS MOV
...

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

Comments (6) |

Print |

Send |

del.icio.us |

Digg It! |

Linking Blogs | 3324 Views

Pearl of Wisdom

Homer no function beer well without.

—Homer Simpson

Calendar