Home > mailing lists

Thread:

From

"Kapil Tilwani"

Date:

29 June 2001, 07:50:09

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

RE:

From

"tamsin"

Date:

29 June 2001, 12:50:16

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

Re:

From

"Thomas T. Veldhouse"

Date:

29 June 2001, 15:10:46

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

Re:

From

"Thomas T. Veldhouse"

Date:

29 June 2001, 16:07:29

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

Re:

From

"Thomas T. Veldhouse"

Date:

29 June 2001, 16:50:51

I agree entirely.  On all the mailing lists that I am on (10+), not one
appreciates HTML, except the PostgreSQL lists, where I see a lot of it.
Since PostgreSQL primarily runs on *NIX, I thought most would prefer text.

Tom Veldhouse
veldy@veldy.net

----- Original Message -----
From: "Jan Wieck" <JanWieck@Yahoo.com>
To: "Thomas T. Veldhouse" <veldy@veldy.net>
Cc: "tamsin" <tg_mail@bryncadfan.co.uk>; "PostgreSQL-general"
<pgsql-general@postgresql.org>
Sent: Friday, June 29, 2001 12:33 PM
Subject: Re: [GENERAL]


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

Re:

From

Jan Wieck

Date:

29 June 2001, 17:00:43

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

Re: Plain text vs HTML mail

From

Tom Lane

Date:

29 June 2001, 17:25:59

"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

Re:

From

Bruce Momjian

Date:

29 June 2001, 18:23:56

> 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
totext? 
> >
>
>     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

Re:

From

Date:

29 June 2001, 18:32:30

On Fri, Jun 29, 2001 at 12:27:40PM -0500, some SMTP stream spewed forth:
> I agree entirely.  On all the mailing lists that I am on (10+), not one
> appreciates HTML, except the PostgreSQL lists, where I see a lot of it.
> Since PostgreSQL primarily runs on *NIX, I thought most would prefer text.

With what are you agreeing?
I appreciate HTML. I have to; it's my job. HTML e-mail is Whole Other
Vile Beast.
I generally consider HTML email an annoyance, and, honestly, most of
the time I ignore it. I feel like HTML does not belong in the "message"
part of an e-mail. If an HTML file needs to be attached, so be it, I
encourage that even. Especially in a public forum, the essence of what
you are communicating should be the focus, not the layout or the pretty
colors or (Universe forbid) the images.

gh

> Tom Veldhouse
> veldy@veldy.net
>
> > 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