pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedComments on subquery performance
6+ messages / 4 participants
[nested] [flat]
* Comments on subquery performance
@ 2005-02-16 20:16 T- Bone <jbowen333@hotmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: T- Bone @ 2005-02-16 20:16 UTC (permalink / raw)
To: pgsql-sql
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://hotm...
Start enjoying all the benefits of MSN® Premium right now and get the
first two months FREE*.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Comments on subquery performance
@ 2005-02-17 17:55 T- Bone <jbowen333@hotmail.com>
2005-02-17 19:15 ` Re: Comments on subquery performance Richard Huxton <dev@archonet.com>
2005-02-23 16:05 ` Re: Comments on subquery performance Edmund Bacon <ebacon@onesystem.com>
0 siblings, 2 replies; 6+ messages in thread
From: T- Bone @ 2005-02-17 17:55 UTC (permalink / raw)
To: pgsql-sql
(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*.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Comments on subquery performance
2005-02-17 17:55 Comments on subquery performance T- Bone <jbowen333@hotmail.com>
@ 2005-02-17 19:15 ` Richard Huxton <dev@archonet.com>
2005-02-18 18:24 ` Re: Comments on subquery performance T- Bone <jbowen333@hotmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2005-02-17 19:15 UTC (permalink / raw)
To: T- Bone <jbowen333@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Comments on subquery performance
2005-02-17 17:55 Comments on subquery performance T- Bone <jbowen333@hotmail.com>
2005-02-17 19:15 ` Re: Comments on subquery performance Richard Huxton <dev@archonet.com>
@ 2005-02-18 18:24 ` T- Bone <jbowen333@hotmail.com>
2005-02-18 18:44 ` Re: Comments on subquery performance Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 1 reply; 6+ messages in thread
From: T- Bone @ 2005-02-18 18:24 UTC (permalink / raw)
To: dev@archonet.com; +Cc: pgsql-sql
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://hotm...
Start enjoying all the benefits of MSN® Premium right now and get the
first two months FREE*.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Comments on subquery performance
2005-02-17 17:55 Comments on subquery performance T- Bone <jbowen333@hotmail.com>
2005-02-17 19:15 ` Re: Comments on subquery performance Richard Huxton <dev@archonet.com>
2005-02-18 18:24 ` Re: Comments on subquery performance T- Bone <jbowen333@hotmail.com>
@ 2005-02-18 18:44 ` Joel Fradkin <jfradkin@wazagua.com>
0 siblings, 0 replies; 6+ messages in thread
From: Joel Fradkin @ 2005-02-18 18:44 UTC (permalink / raw)
To: 'T- Bone' <jbowen333@hotmail.com>; dev@archonet.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Comments on subquery performance
2005-02-17 17:55 Comments on subquery performance T- Bone <jbowen333@hotmail.com>
@ 2005-02-23 16:05 ` Edmund Bacon <ebacon@onesystem.com>
1 sibling, 0 replies; 6+ messages in thread
From: Edmund Bacon @ 2005-02-23 16:05 UTC (permalink / raw)
To: T- Bone <jbowen333@hotmail.com>; +Cc: pgsql-sql
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://hotm...
> 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>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-02-23 16:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-16 20:16 Comments on subquery performance T- Bone <jbowen333@hotmail.com>
2005-02-17 17:55 Comments on subquery performance T- Bone <jbowen333@hotmail.com>
2005-02-17 19:15 ` Richard Huxton <dev@archonet.com>
2005-02-18 18:24 ` T- Bone <jbowen333@hotmail.com>
2005-02-18 18:44 ` Joel Fradkin <jfradkin@wazagua.com>
2005-02-23 16:05 ` Edmund Bacon <ebacon@onesystem.com>
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