Message-ID: <BAY103-F77BD9495F39A47F1A2935E76E0@phx.gbl>
In-Reply-To: <4214ED42.5040802@archonet.com>
From: "T- Bone" <jbowen333@hotmail.com>
To: dev@archonet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Comments on subquery performance
Date: Fri, 18 Feb 2005 11:24:41 -0700
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

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*.