This also depends in the order you join. If your first join returns a large dataset, and the second a smaller dataset it might be interesting to look at rearranging specific statements so you limit the data you work with as fast as possible. Is this something that is happening in you case?
Micha Schopman Project Manager Modern Media, Databankweg 12 M, 3821 AL Amersfoort Tel 033-4535377, Fax 033-4535388 KvK Amersfoort 39081679, Rabo 39.48.05.380 ------------------------------------------------------------------------ ------------------------------------------------------------------------ ----- Modern Media, Making You Interact Smarter. Onze oplossingen verbeteren de interactie met uw doelgroep. Wilt u meer omzet, lagere kosten of een beter service niveau? Voor meer informatie zie www.modernmedia.nl ------------------------------------------------------------------------ ------------------------------------------------------------------------ ----- -----Original Message----- From: James Holmes [mailto:[EMAIL PROTECTED] Sent: dinsdag 31 mei 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:208016 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
