Received: from veldy.net (w028.z064001117.msp-mn.dsl.cnc.net [64.1.117.28]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5TGWha39859 for ; Fri, 29 Jun 2001 12:32:44 -0400 (EDT) (envelope-from veldy@veldy.net) Received: from HP2500B (localhost.veldy.net [127.0.0.1]) by veldy.net (Postfix) with SMTP id 1F993BAAC; Fri, 29 Jun 2001 11:32:40 -0500 (CDT) Message-ID: <009d01c100b8$ef4b8190$3028680a@tgt.com> From: "Thomas T. Veldhouse" To: "tamsin" , "PostgreSQL-general" References: <003501c100b4$4ea2cd10$3028680a@tgt.com> Subject: Re: Date: Fri, 29 Jun 2001 11:31:22 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_009A_01C1008F.06109D40" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200106/1230 X-Sequence-Number: 11864 This is a multi-part message in MIME format. ------=_NextPart_000_009A_01C1008F.06109D40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Heck, you could refine it this way as well. select pm.* from purchasemaster pm inner join itemmaster im on (pm.transactionid =3D im.transactionid and im.itempriority =3D 2) This might reduce memory usage. I am not sure which will turn out to perfo= rm better in a resource friendly environment. Tom Veldhouse veldy@veldy.net PS There is a lot of HTML email on this list. Is this generally considere= d good? Or should I convert my replies to text? ----- Original Message -----=20 From: Thomas T. Veldhouse=20 To: tamsin ; PostgreSQL-general=20 Sent: Friday, June 29, 2001 10:58 AM Subject: Re: [GENERAL]=20 select pm.* from purchasemaster pm inner join itemmaster im on pm.transactionid =3D im.transactionid where im.itempriority =3D 2 Try this instead. Make sure the appropriate indexes (on pm.transactionid, im.transactionid = and im.itempriority) exist if the tables are of any appreciable size.=20=20 Tom Veldhouse veldy@veldy.net ----- Original Message -----=20 From: tamsin=20 To: pgsql-general@postgresql.org=20 Sent: Friday, June 29, 2001 7:40 AM Subject: RE: [GENERAL]=20 i think performance is better in postgres if you rewrite this sort of q= uery as: select * from purchasemaster where exists (select 1 from itemmaster whe= re itempriority =3D 2 and transactionid =3D purchasemaster.transactionid) hope this helps, tamsin -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@= postgresql.org]On Behalf Of Kapil Tilwani Sent: 28 May 2001 12:27 To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org Subject: [GENERAL]=20 Hi, I have two tables purchasemaster (Master) and ItemMaster (child) link= ed through TransactionID I have a query like select * from purchasemaster where transactionid in (select transacti= onid from itemmaster where itempriority =3D 2) ****I need this query for MS-Access, MS-SQL and Postgres. Because th= e application is supposed to be such that for upto 2-3 users, the applicati= on would be running on MS-Access or MSDE, while for heavier databases i.e.,= greater than 4 , the ideal database would be Postgres Only the problem is ... (correct me if i am wrong, PLEEEEEEEEEEEASE)= that for each tuple in purchasemaster the sub-query is re-evaluated (True = or False????). What I am looking at is whether it is possible to execute t= he query in the sub-query just once, get its value as in "(1,2,5,23,7,90)",= etc. in place of the sub-query and accordingly, execute the main query. Thank you, Kapil ------=_NextPart_000_009A_01C1008F.06109D40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Heck, you could refine it this way as=20 well.
 
select pm.*
from purchasemaster pm
&= nbsp;=20 inner join itemmaster im
    on (pm.transactionid =3D=20 im.transactionid and im.itempriority =3D 2)
 
 
This might reduce memory usage.  I am not sure which will turn ou= t to=20 perform better in a resource friendly environment.
 
Tom Veldhouse
 
PS  There is a lot of HTML email on this list.  Is this gene= rally=20 considered good?  Or should I convert my replies to text?
 

----- Original Message -----
Fro= m:=20 Thomas T. Veld= house=20
To: tamsin ; PostgreSQL-general
Sent: Friday, June 29, 2001 10:58= =20 AM
Subject: Re: [GENERAL]

select pm.*
from purchasemaster pm =20 inner join itemmaster im
    on pm.transactionid =3D=20 im.transactionid
where im.itempriority =3D 2
 
Try this instead.
 
Make sure the appropriate indexes (on=20 pm.transactionid, im.transactionid and im.itempriority) exist if the= =20 tables are of any appreciable size. 
 
Tom Veldhouse
veldy@veldy.net
 
----- Original Message -----
F= rom:=20 tamsin
To: pgsql-general@postgresql.o= rg=20
Sent: Friday, June 29, 2001 7:40= =20 AM
Subject: RE: [GENERAL]

i=20 think performance is better in postgres if you rewrite this sort of que= ry=20 as:
 
select * from purchasemaster where exists=20 (select 1 from itemmaster where itempriority =3D 2 and transaction= id =3D=20 purchasemaster.transactionid)
 
hope this helps,
tamsin
 
-----Original Message-----
From: pgsql-general-owne= r@postgresql.org=20 [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kapil= =20 Tilwani
Sent: 28 May 2001 12:27
To:=20 pgsql-general@postgresql.org; pgsql-sql@postgresql.org
Subject:= =20 [GENERAL]

Hi,
 
I have two tables purchase= master=20 (Master) and ItemMaster (child) linked through TransactionID
 
I have a query like=
select * from purchasemast= er where=20 transactionid in (select transactionid from itemmaster where itemprio= rity=20 =3D 2)
 
****I need this query for= =20 MS-Access, MS-SQL and Postgres.  Because the application is supp= osed=20 to be such that for upto 2-3 users, the application would be running = on=20 MS-Access or MSDE, while for heavier databases i.e., greater than 4 ,= the=20 ideal database would be Postgres
 
Only the problem is ...&nb= sp;=20 (correct me if i am wrong, PLEEEEEEEEEEEASE) that for each tuple in= =20 purchasemaster the sub-query is re-evaluated (True or False????).&nbs= p;=20 What I am looking at is whether it is possible to execute the query i= n the=20 sub-query just once, get its value as in "(1,2,5,23,7,90)", etc. in p= lace=20 of the sub-query and accordingly, execute the main=20 query.
 
Thank you,
Kapil
= ------=_NextPart_000_009A_01C1008F.06109D40--