Received: from cmailg4.svr.pol.co.uk (cmailg4.svr.pol.co.uk [195.92.195.174]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5TCewa27357 for ; Fri, 29 Jun 2001 08:40:58 -0400 (EDT) (envelope-from tg_mail@bryncadfan.co.uk) Received: from modem-943.lemur.dialup.pol.co.uk ([217.135.139.175] helo=parsley) by cmailg4.svr.pol.co.uk with smtp (Exim 3.13 #0) id 15FxZq-0004mQ-00 for pgsql-general@postgresql.org; Fri, 29 Jun 2001 13:40:52 +0100 From: "tamsin" To: Subject: RE: Date: Fri, 29 Jun 2001 13:40:04 +0100 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0002_01C100A1.00AD3310" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <001f01c0e812$33a54b60$1300a8c0@kapil1> Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200106/1212 X-Sequence-Number: 11846 This is a multi-part message in MIME format. ------=_NextPart_000_0002_01C100A1.00AD3310 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit i think performance is better in postgres if you rewrite this sort of query as: select * from purchasemaster where exists (select 1 from itemmaster where itempriority = 2 and transactionid = 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] Hi, I have two tables purchasemaster (Master) and ItemMaster (child) linked through TransactionID I have a query like select * from purchasemaster where transactionid in (select transactionid from itemmaster where itempriority = 2) ****I need this query for MS-Access, MS-SQL and Postgres. Because the application is supposed to be such that for upto 2-3 users, the application 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 the 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_0002_01C100A1.00AD3310 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
i=20 think performance is better in postgres if you rewrite this sort of query= =20 as:
 
select=20 * from purchasemaster where exists (select 1 from itemmaster where=20 itempriority =3D 2 and transactionid =3D=20 purchasemaster.transactionid)
 
hope=20 this helps,
tamsin
 
-----Original Message-----
From:=20 pgsql-general-owner@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 purchasemast= er=20 (Master) and ItemMaster (child) linked through TransactionID
 
I have a query like
select * from purchasemaster w= here=20 transactionid in (select transactionid from itemmaster where itempriority= =3D=20 2)
 
****I need this query for MS-A= ccess,=20 MS-SQL and Postgres.  Because the application is supposed to be such= that=20 for upto 2-3 users, the application would be running on MS-Access or MSDE= ,=20 while for heavier databases i.e., greater than 4 , the ideal database wou= ld be=20 Postgres
 
Only the problem is ...  = (correct=20 me if i am wrong, PLEEEEEEEEEEEASE) that for each tuple in purchasemaster= the=20 sub-query is re-evaluated (True or False????).  What I am looking at= is=20 whether it is possible to execute the query in the sub-query just once, g= et=20 its value as in "(1,2,5,23,7,90)", etc. in place of the sub-query and=20 accordingly, execute the main query.
 
Thank you,
Kapil
------=_NextPart_000_0002_01C100A1.00AD3310--