Thread: Comments on subquery performance
(second attempt in two days to post this message...I appologise if for some reason a duplicate appears) Hello all, I created a query that contains two subqueries and joins and would like some feedback on whether: 1) this approach is logical; and, 2) if this is an optimal approach (performance wise) to return therecords I seek. Essentially, I am attempting to perform a 'lookup' on a value in another table 3 times, for three different columns. I have three columns with category codes in tblListings and would like to 'lookup' the actual category text in tblCategory. I have created a functional query that contains two subqueries and joins, but am concerned this may not be the fastest (or logical?) way to achieve what I seek. I thought of another approach to create a function to evaluate the records on a row-by-row and column-by-column approach, but thought that may prove even slower. I would appreciate any comments on my logic or learning of any alternative means that would result in better performance. I have included the SQL for reference. Thanks in advance. Regards, Jim ----------------------------------------8<------------------------------------------------- SELECT "CatID1", "CatID2", "CatID3", c1 AS "CatName1", c2 AS "CatName2", t6."CatName" AS "CatName3"FROM(SELECT "CatID1", "CatID2", "CatID3", c1, t4."CatName" AS c2 FROM (SELECT t1."CatID1",t1."CatID2", t1."CatID3", t2."CatName" AS c1 FROM "MySchema"."tblListings" t1 INNER JOIN "MySchema"."tblCategories"t2 ON (t1."CatID1" = t2."CatID")) t3 LEFT OUTER JOIN "MySchema"."tblCategories" t4 ON (t3."CatID2" = t4."CatID")) t5 LEFT OUTER JOIN "MySchema"."tblCategories" t6 ON (t5."CatID3" = t6."CatID"); ----------------------------------------8<------------------------------------------------- _________________________________________________________________ Take advantage of powerful junk e-mail filters built on patented Microsoft� SmartScreen Technology. http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN� Premium right now and get the first two months FREE*.
T- Bone wrote: > (second attempt in two days to post this message...I appologise if for > some reason a duplicate appears) > > Hello all, > > I created a query that contains two subqueries and joins and would like > some feedback on whether: > 1) this approach is logical; and, > 2) if this is an optimal approach (performance wise) to return the > records I seek. Well you could just do: SELECT l.*, c1.catname, c2.catname, c3.catname FROM tbl_listing l, tbl_categories c1, tbl_categories c2, tbl_categories c3 WHERE l.catid1 = c1.catid AND l.catid2 = c2.catid AND l.catid3 = c3.catid -- Richard Huxton Archonet Ltd
Hi Richard and group, Thanks for the alternative approach. The code is certainly cleaner and easier to follow, but I do have a couple outer joins for fields #2 and #3 that could contain null values that are not captured in your example. Also, I ran an 'explain query' and the performance differences were negligable. Any further thoughts or should I just stick with what I have and move on? Thanks in advance. Cheers, Jim >From: Richard Huxton <dev@archonet.com> >To: T- Bone <jbowen333@hotmail.com> >CC: pgsql-sql@postgresql.org >Subject: Re: [SQL] Comments on subquery performance >Date: Thu, 17 Feb 2005 19:15:14 +0000 > >T- Bone wrote: >>(second attempt in two days to post this message...I appologise if for >>some reason a duplicate appears) >> >>Hello all, >> >>I created a query that contains two subqueries and joins and would like >>some feedback on whether: >> 1) this approach is logical; and, >> 2) if this is an optimal approach (performance wise) to return the >>records I seek. > >Well you could just do: > >SELECT > l.*, c1.catname, c2.catname, c3.catname >FROM > tbl_listing l, > tbl_categories c1, > tbl_categories c2, > tbl_categories c3 >WHERE > l.catid1 = c1.catid > AND l.catid2 = c2.catid > AND l.catid3 = c3.catid > >-- > Richard Huxton > Archonet Ltd > >---------------------------(end of broadcast)--------------------------- >TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org _________________________________________________________________ Scan and help eliminate destructive viruses from your inbound and outbound e-mail and attachments. http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN� Premium right now and get the first two months FREE*.
Yea we examined it here as a group as we are facing the same kind of stuff and found exactly the same thing. It does what MSSQL called a Cartesian join, and ran no faster other then removing the outer join logic. Using a regular join statement and only inner joins was the same speed and I think little less confusing when you have several joins as our system does. We may have to look at re-engineering our system as the original design has dozens of table whith a field set like ID, Value to hold stuff like apprehension type, jobtitle etc. So we hold a main record with dozens of id's (and joins when reporting). Joel Fradkin
Typically, this is handled as (omitting '"'s and schema names for clarity) SELECT CatID1, CatID2, CatID3, t1.CatName as CatName1, t2.CatName as CatName2, t3.CatName as CatName3 FROM tblListings JOIN tblCategories t1 on CatId1 = CatID JOIN tblCategories t2 on CatId2 = CatID JOIN tblCategories t3 on CatId3 = CatID; Wether you need to LEFT JOIN tblCategories or not depends on how you want to do if CatID1, CatID2, or CatID3 is NULL. T- Bone wrote: > (second attempt in two days to post this message...I appologise if for > some reason a duplicate appears) > > Hello all, > > I created a query that contains two subqueries and joins and would > like some feedback on whether: > 1) this approach is logical; and, > 2) if this is an optimal approach (performance wise) to return the > records I seek. > > Essentially, I am attempting to perform a 'lookup' on a value in > another table 3 times, for three different columns. I have three > columns with category codes in tblListings and would like to 'lookup' > the actual category text in tblCategory. I have created a functional > query that contains two subqueries and joins, but am concerned this > may not be the fastest (or logical?) way to achieve what I seek. > > I thought of another approach to create a function to evaluate the > records on a row-by-row and column-by-column approach, but thought > that may prove even slower. I would appreciate any comments on my > logic or learning of any alternative means that would result in better > performance. > > I have included the SQL for reference. Thanks in advance. > > Regards, > Jim > > ----------------------------------------8<------------------------------------------------- > > > SELECT "CatID1", "CatID2", "CatID3", c1 AS "CatName1", c2 AS > "CatName2", t6."CatName" AS "CatName3" > FROM > (SELECT "CatID1", "CatID2", "CatID3", c1, t4."CatName" AS c2 > FROM > (SELECT t1."CatID1", t1."CatID2", t1."CatID3", t2."CatName" AS c1 > FROM "MySchema"."tblListings" t1 > INNER JOIN "MySchema"."tblCategories" t2 > ON (t1."CatID1" = t2."CatID")) t3 > LEFT OUTER JOIN "MySchema"."tblCategories" t4 > ON (t3."CatID2" = t4."CatID")) t5 > LEFT OUTER JOIN "MySchema"."tblCategories" t6 > ON (t5."CatID3" = t6."CatID"); > > ----------------------------------------8<------------------------------------------------- > > > _________________________________________________________________ > Take advantage of powerful junk e-mail filters built on patented > Microsoft® SmartScreen Technology. > http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines > Start enjoying all the benefits of MSN® Premium right now and get the > first two months FREE*. > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Edmund Bacon <ebacon@onesystem.com>