The difference isn't as large as I first thought, but it is still
significant. Here is the real statement (apologies if the formatting
gets mangled a bit):
SELECT escunits.unitname, SUM (wrkcalcs.score)as WDMSCORE,
wrkenrolments.eftsu,
wrkperiods.periodname, wrklocations.LOCATION
FROM wrklocations, escunits, wrkcalcs, wrkperiods, wrkenrolments
WHERE ( (wrklocations.locationid = wrkcalcs.locationid)
AND (wrkperiods.periodid = wrkcalcs.periodid)
AND (escunits.unitid = wrkcalcs.unitid)
AND (escunits.unitid = wrkenrolments.unitid)
AND (wrkperiods.periodid = wrkenrolments.periodid)
AND (wrklocations.locationid = 1)
AND (wrkcalcs.periodid IN (4, 6))
)
GROUP BY wrkperiods.periodname,
escunits.unitname,
wrklocations.LOCATION,
wrkenrolments.eftsu
The joins are all straightforward and the tables contain what they sound
like they contain. It takes 4.3 seconds (or so) to run. I'll send the
explain output later on if necessary.
Here is the statement that runs in 800 ms or less:
SELECT (SELECT UNITNAME FROM ESCUNITS WHERE ESCUNITS.UNITID =
WC.UNITID) AS UNITNAME,
SUM (wc.score) as WDMSCORE,
(SELECT EFTSU FROM WRKENROLMENTS WHERE
WRKENROLMENTS.UNITID = WC.UNITID AND WRKENROLMENTS.PERIODID =
WC.PERIODID) AS EFTSU,
(SELECT PERIODNAME FROM WRKPERIODS WHERE
WRKPERIODS.PERIODID = WC.PERIODID) AS PERIOD,
(SELECT LOCATION FROM WRKLOCATIONS WHERE
WRKLOCATIONS.LOCATIONID = WC.LOCATIONID) AS LOCATION
FROM wrkcalcs WC
WHERE PERIODID IN (4,6)
AND LOCATIONID = 1
GROUP BY wc.periodid, wc.unitid, wc.locationid
The end result is the same dataset. I did notice that the subquery
version allows for one less group by expression, which may or not make
the difference.
I can elaborate on the contents of the tables if necessary.
-----Original Message-----
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 31 May 2005 8:27
To: CF-Talk
Subject: Re: (SOT) Joins aren't always better than subqueries (longish
post)
Micha Schopman wrote:
> 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.
The provided example was very straightforward. If it was representative
the optimizer should have been able to figure that out.
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:208029
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
