Just a comment, there's often a similar time deficit with using SQL joins on Ingres.
-----Original Message----- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: 31 May 2005 10:59 To: CF-Talk Subject: (SOT) Joins aren't always better than subqueries (longish post) 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. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208011 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
