pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRE:
7+ messages / 5 participants
[nested] [flat]
* RE:
@ 2001-06-29 12:40 tamsin <tg_mail@bryncadfan.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: tamsin @ 2001-06-29 12:40 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re:
@ 2001-06-29 15:58 Thomas T. Veldhouse <veldy@veldy.net>
parent: tamsin <tg_mail@bryncadfan.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas T. Veldhouse @ 2001-06-29 15:58 UTC (permalink / raw)
To: tamsin <tg_mail@bryncadfan.co.uk>; PostgreSQL-general <pgsql-general@postgreSQL.org>
select pm.*
from purchasemaster pm
inner join itemmaster im
on pm.transactionid = im.transactionid
where im.itempriority = 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.
Tom Veldhouse
veldy@veldy.net
----- Original Message -----
From: tamsin
To: pgsql-general@postgresql.org
Sent: Friday, June 29, 2001 7:40 AM
Subject: RE: [GENERAL]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re:
@ 2001-06-29 16:31 Thomas T. Veldhouse <veldy@veldy.net>
parent: Thomas T. Veldhouse <veldy@veldy.net>
0 siblings, 2 replies; 7+ messages in thread
From: Thomas T. Veldhouse @ 2001-06-29 16:31 UTC (permalink / raw)
To: tamsin <tg_mail@bryncadfan.co.uk>; PostgreSQL-general <pgsql-general@postgreSQL.org>
Heck, you could refine it this way as well.
select pm.*
from purchasemaster pm
inner join itemmaster im
on (pm.transactionid = im.transactionid and im.itempriority = 2)
This might reduce memory usage. I am not sure which will turn out to perform 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 considered good? Or should I convert my replies to text?
----- Original Message -----
From: Thomas T. Veldhouse
To: tamsin ; PostgreSQL-general
Sent: Friday, June 29, 2001 10:58 AM
Subject: Re: [GENERAL]
select pm.*
from purchasemaster pm
inner join itemmaster im
on pm.transactionid = im.transactionid
where im.itempriority = 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.
Tom Veldhouse
veldy@veldy.net
----- Original Message -----
From: tamsin
To: pgsql-general@postgresql.org
Sent: Friday, June 29, 2001 7:40 AM
Subject: RE: [GENERAL]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re:
@ 2001-06-29 17:33 Jan Wieck <JanWieck@Yahoo.com>
parent: Thomas T. Veldhouse <veldy@veldy.net>
1 sibling, 1 reply; 7+ messages in thread
From: Jan Wieck @ 2001-06-29 17:33 UTC (permalink / raw)
To: Thomas T. Veldhouse <veldy@veldy.net>; +Cc: tamsin <tg_mail@bryncadfan.co.uk>; PostgreSQL-general <pgsql-general@postgresql.org>
Thomas T. Veldhouse wrote:
>
> PS There is a lot of HTML email on this list. Is this generally considered good? Or should I convert my replies to text?
>
This is generally considered annoying, as lines >80
characters are :-P
Wasn't meant offensive - just couldn't resist.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Plain text vs HTML mail
@ 2001-06-29 18:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas T. Veldhouse <veldy@veldy.net>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-06-29 18:01 UTC (permalink / raw)
To: Thomas T. Veldhouse <veldy@veldy.net>; +Cc: tamsin <tg_mail@bryncadfan.co.uk>; PostgreSQL-general <pgsql-general@postgresql.org>
"Thomas T. Veldhouse" <veldy@veldy.net> writes:
> 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?
This is generally considered evil. Plain text messages are preferred.
HTML is harder to quote when replying, and the variants that send both
HTML and plain text bloat the archives far more than necessary.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re:
@ 2001-06-29 18:31 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Jan Wieck <JanWieck@Yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 2001-06-29 18:31 UTC (permalink / raw)
To: Jan Wieck <JanWieck@Yahoo.com>; +Cc: Thomas T. Veldhouse <veldy@veldy.net>; tamsin <tg_mail@bryncadfan.co.uk>; PostgreSQL-general <pgsql-general@postgresql.org>
> Thomas T. Veldhouse wrote:
> >
> > PS There is a lot of HTML email on this list. Is this generally considered good? Or should I convert my replies to text?
> >
>
> This is generally considered annoying, as lines >80
> characters are :-P
>
> Wasn't meant offensive - just couldn't resist.
Let's not forget email with no subject, like this one.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: (was: )
@ 2001-06-29 18:47 Jan Wieck <JanWieck@Yahoo.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Jan Wieck @ 2001-06-29 18:47 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Jan Wieck <JanWieck@Yahoo.com>; Thomas T. Veldhouse <veldy@veldy.net>; tamsin <tg_mail@bryncadfan.co.uk>; PostgreSQL-general <pgsql-general@postgresql.org>
Bruce Momjian wrote:
> > Thomas T. Veldhouse wrote:
> > >
> > > PS There is a lot of HTML email on this list. Is this generally considered good? Or should I convert my replies to text?
> > >
> >
> > This is generally considered annoying, as lines >80
> > characters are :-P
> >
> > Wasn't meant offensive - just couldn't resist.
>
> Let's not forget email with no subject, like this one.
Come on, let's create some mega-off-topic thread about if
"Re: [GENERAL]" is a subject or not. :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-06-29 18:47 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-29 12:40 RE: tamsin <tg_mail@bryncadfan.co.uk>
2001-06-29 15:58 ` Re: Thomas T. Veldhouse <veldy@veldy.net>
2001-06-29 16:31 ` Re: Thomas T. Veldhouse <veldy@veldy.net>
2001-06-29 17:33 ` Re: Jan Wieck <JanWieck@Yahoo.com>
2001-06-29 18:31 ` Re: Bruce Momjian <pgman@candle.pha.pa.us>
2001-06-29 18:47 ` Re: (was: ) Jan Wieck <JanWieck@Yahoo.com>
2001-06-29 18:01 ` Re: Plain text vs HTML mail Tom Lane <tgl@sss.pgh.pa.us>
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