Re: (SOT) Joins aren't always better than subqueries (longish post)
James Holmes wrote: My quick query (800ms): SELECT STATEMENT Optimizer Mode=CHOOSE TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS TABLE ACCESS FULL DIVWEB.WRKENROLMENTS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS SORT GROUP BY TABLE ACCESS FULL DIVWEB.WRKCALCS Let's start with this one. What Oracle does here is first a full table scan of the WRKCACLS table, then a sort and group by. Then for each result of that it passes all the other tables to search for matching rows. The obvious reason why this is so fast is that many rows get discarded early on. I wonder why the unique index on WRKENROLLMENTS (periodid, unitid) isn't used. Is the WRKENROLLMENTS table very small perhaps? Your query ran in 2.6 seconds. The explain plan for that is: SELECT STATEMENT Optimizer Mode=CHOOSE SORT GROUP BY NESTED LOOPS MERGE JOIN SORT JOIN NESTED LOOPS NESTED LOOPS TABLE ACCESS FULL DIVWEB.WRKENROLMENTS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS SORT JOIN TABLE ACCESS FULL DIVWEB.WRKCALCS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS My slower query (4.3 secs): SELECT STATEMENT Optimizer Mode=CHOOSE SORT GROUP BY MERGE JOIN SORT JOIN NESTED LOOPS NESTED LOOPS MERGE JOIN TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS FILTER TABLE ACCESS FULL DIVWEB.WRKCALCS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS SORT JOIN TABLE ACCESS FULL DIVWEB.WRKENROLMENTS As you can see in both of these the SORT GROUP BY is the last step. So in all intermediate steps the database has many more rows, which makes it slower. Jochem ~| 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:208208 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
Ah, the PK on wrkenrolments is actually a separate column (enrolmentid) so there isn't an index to use. Maybe I should define a composite key for that purpose instead? -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, 1 June 2005 10:59 To: CF-Talk Subject: Re: (SOT) Joins aren't always better than subqueries (longish post) James Holmes wrote: My quick query (800ms): SELECT STATEMENT Optimizer Mode=CHOOSE TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS TABLE ACCESS FULL DIVWEB.WRKENROLMENTS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS SORT GROUP BY TABLE ACCESS FULL DIVWEB.WRKCALCS Let's start with this one. What Oracle does here is first a full table scan of the WRKCACLS table, then a sort and group by. Then for each result of that it passes all the other tables to search for matching rows. The obvious reason why this is so fast is that many rows get discarded early on. I wonder why the unique index on WRKENROLLMENTS (periodid, unitid) isn't used. Is the WRKENROLLMENTS table very small perhaps? [snip] ~| 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:208210 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
Re: (SOT) Joins aren't always better than subqueries (longish post)
James Holmes wrote: Ah, the PK on wrkenrolments is actually a separate column (enrolmentid) so there isn't an index to use. Maybe I should define a composite key for that purpose instead? Yes, you very, very much should so. And not for the purpose of speed, but for the purpose of data integrity. Your query with subselects will throw an exception if there are multiple rows with the same periodid and unitid in the WRKENROLLMENTS table. Jochem ~| 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:208213 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
Yes, I found that out the hard way (bad data import). I'll drop the existing PK and create a composite key. I'll also see if that speeds things up any more. Thanks muchly for the help too. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, 1 June 2005 11:13 To: CF-Talk Subject: Re: (SOT) Joins aren't always better than subqueries (longish post) James Holmes wrote: Ah, the PK on wrkenrolments is actually a separate column (enrolmentid) so there isn't an index to use. Maybe I should define a composite key for that purpose instead? Yes, you very, very much should so. And not for the purpose of speed, but for the purpose of data integrity. Your query with subselects will throw an exception if there are multiple rows with the same periodid and unitid in the WRKENROLLMENTS table. ~| 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:208214 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
Re: (SOT) Joins aren't always better than subqueries (longish post)
James Holmes wrote: Yes, I found that out the hard way (bad data import). I'll drop the existing PK and create a composite key. You don't have to drop the existing PK. Just add another unique index. Jochem ~| 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:208217 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
Of course, but I'm wondering if the overhead of an extra index on inserts is worth it; I guess having a unique single ID for each enrolment is still a Good Thing(tm). The amount of data is so small that it the extra index will probably make little difference to inserts. -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: Wednesday, 1 June 2005 11:25 To: CF-Talk Subject: Re: (SOT) Joins aren't always better than subqueries (longish post) James Holmes wrote: Yes, I found that out the hard way (bad data import). I'll drop the existing PK and create a composite key. You don't have to drop the existing PK. Just add another unique index. Jochem ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:208221 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
Well, in addition to providing th data integrity I was after, adding the composite index also halved the time the query took to run; now it's 400 ms. Thanks again. ~| 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:208225 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
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
Re: (SOT) Joins aren't always better than subqueries (longish post)
James Holmes 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. It is. But it is getting less important as optimizers get better. Anyone more intimate with the inner dark secrets of SQL is welcome to comment. If you are looking for an explanation, show use the real SQL and the EXPLAIN output of the queries. Jochem ~| 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:208015 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
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
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 ~| 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:208022 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
Re: (SOT) Joins aren't always better than subqueries (longish post)
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
RE: (SOT) Joins aren't always better than subqueries (longish post)
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
Re: (SOT) Joins aren't always better than subqueries (longish post)
James Holmes wrote: 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. Please do so. I expect that it will show that the joined version is driven by the wrklocations table and the subquery version is driven by the wrkcalcs table. 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 don't think that explains the difference. How fast is this query? And what does the explain output show? 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 (wrkcalcs.locationid = 1) AND (wrkcalcs.periodid IN (4, 6)) GROUP BY wrkperiods.periodname, escunits.unitname, wrklocations.LOCATION, wrkenrolments.eftsu Jochem ~| 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:208032 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
RE: (SOT) Joins aren't always better than subqueries (longish post)
Your query ran in 2.6 seconds. The explain plan for that is: SELECT STATEMENT Optimizer Mode=CHOOSE SORT GROUP BY NESTED LOOPS MERGE JOIN SORT JOIN NESTED LOOPS NESTED LOOPS TABLE ACCESS FULL DIVWEB.WRKENROLMENTS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS SORT JOIN TABLE ACCESS FULL DIVWEB.WRKCALCS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS My slower query (4.3 secs): SELECT STATEMENT Optimizer Mode=CHOOSE SORT GROUP BY MERGE JOIN SORT JOIN NESTED LOOPS NESTED LOOPS MERGE JOIN TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS FILTER TABLE ACCESS FULL DIVWEB.WRKCALCS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS SORT JOIN TABLE ACCESS FULL DIVWEB.WRKENROLMENTS My quick query (800ms): SELECT STATEMENT Optimizer Mode=CHOOSE TABLE ACCESS BY INDEX ROWID DIVWEB.ESCUNITS INDEX UNIQUE SCAN DIVWEB.PK_ESCUNITS TABLE ACCESS FULL DIVWEB.WRKENROLMENTS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKPERIODS INDEX UNIQUE SCAN DIVWEB.PKWRKPERIODS TABLE ACCESS BY INDEX ROWID DIVWEB.WRKLOCATIONS INDEX UNIQUE SCAN DIVWEB.PK_WRKLOCATIONS SORT GROUP BY TABLE ACCESS FULL DIVWEB.WRKCALCS ~| 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:208035 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
