pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
RE:
7+ messages / 5 participants
[nested] [flat]

* RE:
@ 2001-06-29 12:40 tamsin <tg_mail@bryncadfan.co.uk>
  2001-06-29 15:58 ` Re: Thomas T. Veldhouse <veldy@veldy.net>
  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 12:40 RE: tamsin <tg_mail@bryncadfan.co.uk>
@ 2001-06-29 15:58 ` Thomas T. Veldhouse <veldy@veldy.net>
  2001-06-29 16:31   ` Re: Thomas T. Veldhouse <veldy@veldy.net>
  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 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   ` Thomas T. Veldhouse <veldy@veldy.net>
  2001-06-29 17:33     ` Re: Jan Wieck <JanWieck@Yahoo.com>
  2001-06-29 18:01     ` Re: Plain text vs HTML mail Tom Lane <tgl@sss.pgh.pa.us>
  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 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     ` Jan Wieck <JanWieck@Yahoo.com>
  2001-06-29 18:31       ` Re: Bruce Momjian <pgman@candle.pha.pa.us>
  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:
  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       ` Bruce Momjian <pgman@candle.pha.pa.us>
  2001-06-29 18:47         ` Re: (was: ) 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 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         ` Jan Wieck <JanWieck@Yahoo.com>
  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

* Re: Plain text vs HTML mail
  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 18:01     ` Tom Lane <tgl@sss.pgh.pa.us>
  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


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