From: T- Bone <jbowen333@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Comments on subquery performance
Date: Thu, 17 Feb 2005 10:55:30 -0700
Message-ID: <BAY103-F17FF599D1FD15D1E06780BE76D0@phx.gbl> (raw)
(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://hotm...
Start enjoying all the benefits of MSN® Premium right now and get the
first two months FREE*.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jbowen333@hotmail.com
Subject: Re: Comments on subquery performance
In-Reply-To: <BAY103-F17FF599D1FD15D1E06780BE76D0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox