List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:May 29 2006 9:17am
Subject:Re: ugly SQL for a report...
View as plain text  
On 5/29/06, Cory wrote:
> I have the following query that is running VERY slowly.   Anyone have
> any suggestions?
>
> ---
> SELECT pnr.ID ID_pnr, pnr.reservationdatetime, pnr.conf_number,
> pnr.created_by, GROUP_CONCAT(pp.name_last,', ',pp.name_first ORDER BY
> name_last DESC SEPARATOR '<br>') names, (SELECT SUM(pf.base_fare*(SELECT
> COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
> pf WHERE pf.ID_pnr=pnr.ID ) base_fare, (SELECT SUM(pf.tax*(SELECT
> COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM pnr_fares
> pf WHERE pf.ID_pnr=pnr.ID ) tax , (SELECT SUM(psf.segfee_amount*(SELECT
> COUNT(1) FROM pnr_passengers pp WHERE pp.ID_pnr=pnr.ID )) FROM
> pnr_segments_fees psf INNER JOIN pnr_segments pss ON
> psf.ID_segments=pss.ID WHERE psf.segfee_code='XF' AND pss.ID_pnr=pnr.ID
> ) XF ,(SELECT SUM(amount_value) service_fees FROM pnr_service_fees WHERE
> ID_pnr=pnr.ID) services ,(SELECT SUM(amount) FROM pnr_payments_credits
> WHERE ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='sale')
> payments, (SELECT SUM(amount) FROM pnr_payments_credits WHERE
> ID_pnr=pnr.ID AND bln_payment='1' AND transaction_type='credit') credits
> FROM pnr INNER JOIN pnr_passengers pp ON pnr.ID=pp.ID_pnr WHERE
> pnr.reservationdatetime>='2000-05-29 00:00:00' AND
> pnr.reservationdatetime<='2006-05-29 23:59:59' GROUP BY pnr.ID ORDER BY
> reservationdatetime;

That is indeed very ugly. If you prettify it by adding proper
indenting etc. I might be inclined to read it and think about an
answer.

Jochem
Thread
ugly SQL for a report...Cory29 May
  • Re: ugly SQL for a report...Jochem van Dieten29 May
  • Re: ugly SQL for a report...Cory Robin29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
      • Re: ugly SQL for a report...Cory Robin30 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Jochem van Dieten29 May
    • Re: ugly SQL for a report...Peter Brawley30 May