X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 33CB79FA5DD for ; Thu, 29 Jun 2006 21:38:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 93221-07 for ; Thu, 29 Jun 2006 21:38:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from weatherbeeta.com.au (mail.weatherbeeta.com.au [202.154.127.242]) by postgresql.org (Postfix) with SMTP id B5D4D9FA382 for ; Thu, 29 Jun 2006 21:38:22 -0300 (ADT) Received: (qmail 31908 invoked from network); 30 Jun 2006 00:38:19 -0000 X-NetworkBox-HamSign: 0101;OUT;wbeetaau1;487495b9c9894c9f1dcc06c22ad45456; Received: from mail.weatherbeeta.com.au (HELO wb-email) (172.20.0.2)by network-box.weatherbeeta.com.au with SMTP; 30 Jun 2006 00:38:19 -0000 Received: from it-phil.weatherbeeta.com.au ([172.20.0.155]) by wb-email (602Pro LAN SUITE 2003) id 2d54b289 for pgsql-sql@postgresql.org; Fri, 30 Jun 2006 10:35:55 +1000 From: "Phillip Smith" To: Subject: Re: SELECT Aggregate Date: Fri, 30 Jun 2006 10:37:24 +1000 Message-ID: <004801c69bdd$5b6c4de0$9b0014ac@ITPhil> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0049_01C69C31.2D185DE0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 In-Reply-To: Importance: Normal X-Scanned-By-wbeetaau1: Virus scan performed by network-box X-Scanned-By-wbeetaau1: Scanner file id is wbeetaau1-1151627899.286-31892-000 X-Scanned-By-wbeetaau1: No known viruses found in message (received+scanned in 0.04/0.43 secs) X-Scanned-By-wbeetaau1: Spam-Check-Result: No, hits=0 required=7 tests= autolearn=no version=2.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/325 X-Sequence-Number: 25561 This is a multi-part message in MIME format. ------=_NextPart_000_0049_01C69C31.2D185DE0 Content-Type: text/plain;charset="us-ascii" Content-Transfer-Encoding: quoted-printable I've tried Aaron's suggestion of the GROUP BY and I don't know much = about it, but it ran for around 17 hours and still going (it had a dedicated = Dual Xeon 3.0GHz box under RHEL4 running it!) =20 I'll give Richard's suggestion a try and see if that comes up any = better. Like I said yesterday, this might just be too much for Postgres and I'll need to summarize it in the export from our live system and add a new = column before I import it to the sales_orders table =20 Cheers, -p =20 -----Original Message----- From: aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On Behalf Of = Aaron Bono Sent: Friday, 30 June 2006 01:25 To: Richard Broersma Jr Cc: Phillip Smith; pgsql-sql@postgresql.org Subject: Re: [SQL] SELECT Aggregate =20 I am not familiar enough with how postgres optimizes the queries but = won't this end up with=20 total number of queries run on DB =3D 1 query + 1 query/row in first = query What would be more efficient on a large database - a query like Richard submitted (subquery in the select) or one like I submitted (join the two tables and then do a group by)? My guess is it depends on the % of = records returned out of the sales_orders table, the smaller the % the better Richard's query would perform, the higher the % the better the join = would run.=20 The database I am working with aren't big enough yet to warrant spending = a lot of time researching this but if someone with more experience knows = what is best I would love to hear about it. Thanks, Aaron Bono=20 On 6/29/06, Richard Broersma Jr wrote: > SELECT trans_no, > customer, > date_placed, > date_complete, > date_printed, > ord_type, > ord_status, select ( SUM(sell_price) from soh_product where sales_orders.trans_no =3D = soh_product.soh_num ) as transact_sum,=20 > customer_reference, > salesman, > parent_order, > child_order, > order_number > FROM sales_orders > WHERE (trans_no Like '8%' AND order_number Like '8%')=20 > OR (trans_no Like '9%' AND order_number Like '8%') > OR (trans_no Like '8%' AND order_number Like '9%') > OR (trans_no Like '9%' AND order_number Like '9%') > AND (warehouse=3D'M')=20 > AND (date_placed > (current_date + ('12 months = ago'::interval))) > ORDER BY trans_no DESC *******************Confidentiality and Privilege Notice******************* The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments ------=_NextPart_000_0049_01C69C31.2D185DE0 Content-Type: text/html;charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I’ve tried Aaron’s = suggestion of the GROUP BY and I don’t know much about it, but it ran for around = 17 hours and still going (it had a dedicated Dual Xeon 3.0GHz box under = RHEL4 running it!)

 

I’ll give Richard’s = suggestion a try and see if that comes up any better. Like I said yesterday, this = might just be too much for Postgres and I’ll need to summarize it in the = export from our live system and add a new column before I import it to the = sales_orders table

 

Cheers,

-p

 

-----Original Message-----
From: = aaron.bono@gmail.com [mailto:aaron.bono@gmail.com] On = Behalf Of Aaron Bono
Sent: Friday, 30 June 2006 01:25
To: Richard Broersma = Jr
Cc: Phillip Smith; pgsql-sql@postgresql.org
Subject: Re: [SQL] SELECT Aggregate

 

I am not familiar enough with how postgres optimizes the queries but won't = this end up with

total number of queries run on DB =3D 1 query + 1 query/row in first = query

What would be more efficient on a large database - a query like Richard submitted (subquery in the select) or one like I submitted (join the two = tables and then do a group by)?  My guess is it depends on the % of = records returned out of the sales_orders table, the smaller the % the better = Richard's query would perform, the higher the % the better the join would run. =

The database I am working with aren't big enough yet to warrant spending = a lot of time researching this but if someone with more experience knows what = is best I would love to hear about it.

Thanks,
Aaron Bono

On = 6/29/06, Richard Broersma Jr <rabroersma@yahoo.com> wrote:

> SELECT      trans_no,
>           &nb= sp; customer,
>           &nb= sp; date_placed,
>           &nb= sp; date_complete,
>           &nb= sp; date_printed,
>           &nb= sp; ord_type,
>           &nb= sp; ord_status,
            &= nbsp; select (
            &= nbsp;            SUM(sell_price)
            &= nbsp;            from soh_product
            &= nbsp;            where sales_orders.trans_no =3D soh_product.soh_num
            &= nbsp;         ) as transact_sum,
>           &nb= sp; customer_reference,
>           &nb= sp; salesman,
>           &nb= sp; parent_order,
>           &nb= sp; child_order,
>           &nb= sp; order_number
> = FROM        sales_orders
> WHERE       (trans_no Like '8%' AND order_number Like '8%')
>  OR         = (trans_no Like '9%' AND order_number Like '8%')
>  OR         = (trans_no Like '8%' AND order_number Like '9%')
>  OR         = (trans_no Like '9%' AND order_number Like '9%')
>  AND        (wareh= ouse=3D'M')
>  AND        (date_= placed > (current_date + ('12 months ago'::interval)))
> ORDER BY trans_no DESC


*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email.

Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments
------=_NextPart_000_0049_01C69C31.2D185DE0--