[SQL] 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 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 [EMAIL PROTECTED]
Re: [SQL] Comments on subquery performance
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 To: T- Bone <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] Comments on subquery performance Date: Thu, 17 Feb 2005 19:15:14 + 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 [EMAIL PROTECTED] _ 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*. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Comments on subquery performance
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<- _ MSN® Calendar keeps you organized and takes the effort out of scheduling get-togethers. 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 8: explain analyze is your friend
