you can also use the aggregates as analytics in Oracle, eh? IIRC, something like SELECT tblunit.unitname, SUM(tblscore.score) OVER (PARTITION BY tblscore.unitid) AS totalscore, FROM tblscore, tblunit WHERE tblscore.unitid = tblunit.unitid this avoids the need for groupby, but does add a twist to your result set as usually you need to use the GROUP attribute of cfoutput. I'd imagine it might perform better then a sub query too on more complex examples. DK
On 5/31/05, James Holmes <[EMAIL PROTECTED]> wrote: > > It is common for best practices docs to advise that we should use a join > in preference to a subquery wherever possible, since the subquery > prevents the DB from creating the execution plan it could with a join. > This is probably quite true most of the time. However I just discovered > a situation (in Oracle at least) where joins hobbled my query so badly > that a query that should have taken milliseconds took minutes; using an > aggregate function in a query that needed to return joined info. > > I'll simplify my situation: say I have the following table for scores in > a unit of study: > > tblscore - scoreid, score, personid, unitid > > and it is joined to a tblperson table and a tblunit table in the obvious > way. Now I want to sum the scores across all people in the given unit, > so I do this: > > SELECT SUM(score) AS totalscore > FROM tblscore > GROUP BY unitid > > No problems so far. Great, but I now need to return the unit name in the > above query, so using a join I do this: > > SELECT SUM(tblscore.score) AS totalscore, tblunit.unitname > FROM tblscore, tblunit > WHERE tblscore.unitid = tblunit.unitid > GROUP BY tblunit.unitname > > (yeah yeah, I know, use ANSI join syntax - I'm an Oracle user, so sue > me). > > This is necessary because you must either group or aggregate on anything > returned by the query. The end result performs like a dog in a situation > where there are in fact three more joins to other tables. I presume this > is because to figure out the grouping for the sum the join result has to > be returned and it messes up the aggregate's performance. > > Oracle's subqueries to the rescue: > > SELECT SUM(score) AS totalscore, (SELECT unitname FROM tblunit WHERE > tblunit.unitid = s.unitid) AS unitname > FROM tblscore s > GROUP BY unitid > > This performs many times better than the group on the join, when there > are actually four joins to perform. > > Anyone more intimate with the inner dark secrets of SQL is welcome to > comment. > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208028 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
