Re: The Query From Hell

From: Mtek <mtek_at_mtekusa.com>
Date: Mon, 9 Feb 2009 10:24:10 -0800 (PST)
Message-ID: <ce8f7cb4-8b2f-43e3-bc58-0880d1765f2e_at_v18g2000pro.googlegroups.com>

On Feb 9, 11:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 9, 10:36 am, Mtek <m..._at_mtekusa.com> wrote:
>
> > Hi,
>
> > Well, I just thought I'd share this junk with everyone.  We hired a
> > consulting firm to assist on a project.  And, well, this is one of the
> > queries they came up with, which we intend to re-write, once we figure
> > out what the hell it is doing......
>
> > if($compareType=='c') {
> >   $inQuery="'".$strTicker1."', '".$strTicker2."', '".$strTicker3."',
> > '".$strTicker4."', '".$strTicker5."'";
> >   $getQuery="
> >      select my_left_table.fund_name,
> >             my_left_table.LINK,
> >             DECODE(fund_rank.curr_rank,NULL,'N/A',fund_rank.curr_rank)
> > AS fund_rank,
> >             my_left_table.nav,
> >             my_left_table.TOTAL_RETURN_1YR,
> >             my_left_table.EXPENSE_RATIO,
> >             my_left_table.MARKET_VALUE_AVG,
> >             my_left_table.MGR_START_DATE
> >      from (
> >         SELECT MUTUAL_FUND.FUND_NAME,
> >                MUTUAL_FUND.TICKER,
> >                to_char( decode( FUND_DAILY_PRICES.NAV,to_char
> > (-9999),'N/A',FUND_DAILY_PRICES.NAV ) ) AS NAV,
> >                to_char( decode( FUND_MASTER.TOTAL_RETURN_1YR,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_1YR ) ) AS TOTAL_RETURN_1YR,
> >                to_char( decode( FUND_MASTER.EXPENSE_RATIO,to_char
> > (-9999),'N/A',FUND_MASTER.EXPENSE_RATIO ) ) AS EXPENSE_RATIO,
> >                to_char( decode( FUND_MASTER.MARKET_VALUE_AVG,to_char
> > (-9999),'N/A',FUND_MASTER.MARKET_VALUE_AVG ) ) AS MARKET_VALUE_AVG,
> >                CASE WHEN fund_master.mgr_start_date IS NULL THEN NULL
> > ELSE TO_DATE( TO_CHAR( '01' || '/'||fund_master.mgr_start_date),'dd-mm-
> > RRRR' ) END AS MGR_START_DATE,
> >               ('<a href=\"http://URLHERE?t='||
> > MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.TICKER||'</a>') AS
> > LINK
> >         FROM MUTUAL_FUND, FUND_MASTER, FUND_DAILY_PRICES
> >         where MUTUAL_FUND.TICKER = FUND_DAILY_PRICES.TICKER
> >           AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
> >           AND MUTUAL_FUND.TICKER IN( ".$inQuery." ))
> >         my_left_table left join fund_rank
> >        on my_left_table.ticker = fund_rank.ticker";} else {
>
> >   // Ashis :           "$getQuery" problem in to_date(to_char('01' ||
> > '-'||FUND_MASTER.MGR_START_DATE),'dd-mm-RRRR') function. when
> > FUND_MASTER.MGR_START_DATE value is blank then query not execute. This
> > causes we are added NVL function.
> >   $getQuery="
> >      select my_left_table.fund_name,
> >             my_left_table.LINK,
> >             DECODE(fund_rank.curr_rank,NULL,'N/A',fund_rank.curr_rank)
> > AS fund_rank,
> >             my_left_table.nav,
> >             my_left_table.TOTAL_RETURN_1YR,
> >             my_left_table.EXPENSE_RATIO,
> >             my_left_table.MARKET_VALUE_AVG,
> >             my_left_table.MGR_START_DATE
> >      from (
> >         SELECT MUTUAL_FUND.FUND_NAME,
> >                MUTUAL_FUND.TICKER,
> >                to_char( decode( FUND_DAILY_PRICES.NAV,to_char
> > (-9999),'N/A',FUND_DAILY_PRICES.NAV ) ) AS NAV,
> >                to_char( decode( FUND_MASTER.TOTAL_RETURN_1YR,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_1YR ) ) AS  TOTAL_RETURN_1YR,
> >                to_char( decode( FUND_MASTER.EXPENSE_RATIO,to_char
> > (-9999),'N/A',FUND_MASTER.EXPENSE_RATIO ) ) AS   EXPENSE_RATIO,
> >                to_char( decode( FUND_MASTER.MARKET_VALUE_AVG,to_char
> > (-9999),'N/A',FUND_MASTER.MARKET_VALUE_AVG ) ) AS   MARKET_VALUE_AVG,
> >                CASE WHEN fund_master.mgr_start_date IS NULL THEN NULL
> > ELSE TO_DATE( TO_CHAR( '01' || '/'||fund_master.mgr_start_date),'dd-mm-
> > RRRR' ) END AS MGR_START_DATE,
> >                ('<a href=\"http://URLHERE?t='||
> > MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.TICKER||'</a>') AS
> > LINK
> >         FROM MUTUAL_FUND,
> >              FUND_MASTER,
> >              FUND_DAILY_PRICES
> >         where MUTUAL_FUND.TICKER = FUND_DAILY_PRICES.TICKER
> >           AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
> >           AND MUTUAL_FUND.TICKER = '".$strTicker1."')
> >         my_left_table left join fund_rank
> >         on my_left_table.ticker = fund_rank.ticker
> >      UNION ALL
> >      SELECT FUND_NAME,
> >             ('<a href=\"http://URLHERE?t='||TICKER||'&type=main\">'||
> > TICKER||'</a>'),
> >             DECODE(CURR_RANK,NULL,'N/A',CURR_RANK) AS fund,
> >             to_char(NAV),
> >             to_char(TOTAL_RETURN_1YR),
> >             to_char(EXPENSE_RATIO),
> >             to_char(MARKET_VALUE_AVG),
> >             m
> >      FROM (
> >         SELECT YTD_TICKERS.*,
> >                FUND_MASTER.TOTAL_RETURN_YTD,
> >                row_number() over( order by
> > FUND_MASTER.TOTAL_RETURN_YTD desc ) as ytd_desc
> >         FROM (
> >           SELECT a.*,
> >                  row_number() over( order by to_date(m,'dd-mm-RRRR') )
> > as m_Asc
> >           from (
> >              SELECT MGR_TICKERS.*,
> >                     to_date(to_char('01' || '-'||NVL
> > (FUND_MASTER.MGR_START_DATE, '05/08')),'dd-mm-RRRR') as m
> >              FROM (
> >                SELECT *
> >                FROM (
> >                  SELECT MUTUAL_FUND.FUND_NAME,
> >                         MUTUAL_FUND.TICKER,
> >                         FUND_RANK.CURR_RANK,
> >                         decode(FUND_DAILY_PRICES.NAV,to_char(-9999),'N/
> > A',FUND_DAILY_PRICES.NAV) AS
> > NAV,
> > decode(FUND_MASTER.MARKET_VALUE_AVG,to_char(-9999),'N/
> > A',FUND_MASTER.MARKET_VALUE_AVG) AS
> > MARKET_VALUE_AVG,
> > decode(FUND_MASTER.TOTAL_RETURN_1MT,to_char(-9999),'N/
> > A',FUND_MASTER.TOTAL_RETURN_1MT) AS TOTAL_RETURN_1MT,
> >                         decode(FUND_MASTER.TOTAL_RETURN_3MT,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_3MT) AS TOTAL_RETURN_3MT,
> >                         decode(FUND_MASTER.TOTAL_RETURN_1YR,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_1YR) AS TOTAL_RETURN_1YR,
> >                         decode(FUND_MASTER.TOTAL_RETURN_3YR,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_3YR) AS TOTAL_RETURN_3YR,
> >                         decode(FUND_MASTER.TOTAL_RETURN_5YR,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_5YR) AS TOTAL_RETURN_5YR,
> >                         decode(FUND_MASTER.TOTAL_RETURN_10YR,to_char
> > (-9999),'N/A',FUND_MASTER.TOTAL_RETURN_10YR) AS TOTAL_RETURN_10YR,
> >                         decode(FUND_MASTER.STD_DEV_3YR,to_char
> > (-9999),'N/A',FUND_MASTER.STD_DEV_3YR) AS STD_DEV_3YR,
> >                         decode(FUND_MASTER.SHARPE_3YR,to_char
> > (-9999),'N/A',FUND_MASTER.SHARPE_3YR) AS SHARPE_3YR,
> >                         decode(FUND_MASTER.R_SQUARE_3YR,to_char
> > (-9999),'N/A',FUND_MASTER.R_SQUARE_3YR) AS R_SQUARE_3YR,
> >                         decode(FUND_MASTER.BETA_3YR,to_char(-9999),'N/
> > A',FUND_MASTER.BETA_3YR) AS BETA_3YR,
> >                         decode(FUND_MASTER.ALPHA_3YR,to_char(-9999),'N/
> > A',FUND_MASTER.ALPHA_3YR) AS ALPHA_3YR,
> >                         decode(FUND_MASTER.FEE_12B1_PERC,to_char
> > (-9999),'N/A',FUND_MASTER.FEE_12B1_PERC) AS FEE_12B1_PERC,
> >                         decode(FUND_MASTER.MIN_INIT_INVEST,to_char
> > (-9999),'N/A',FUND_MASTER.MIN_INIT_INVEST) AS MIN_INIT_INVEST,
> >                         decode(FUND_MASTER.MIN_SUBS_INVEST,to_char
> > (-9999),'N/A',FUND_MASTER.MIN_SUBS_INVEST) AS MIN_SUBS_INVEST,
> >                         decode(FUND_MASTER.PORTFOLIO_TURNOVER,to_char
> > (-9999),'N/A',FUND_MASTER.PORTFOLIO_TURNOVER) AS PORTFOLIO_TURNOVER ,
> >                         decode(FUND_MASTER.LARGE_GROWTH,to_char
> > (-9999),'N/A',FUND_MASTER.LARGE_GROWTH) AS LARGE_GROWTH,
> >                         decode(FUND_MASTER.LARGE_VALUE,to_char
> > (-9999),'N/A',FUND_MASTER.LARGE_VALUE) AS LARGE_VALUE,
> >                         decode(FUND_MASTER.SMALL_GROWTH,to_char
> > (-9999),'N/A',FUND_MASTER.SMALL_GROWTH) AS SMALL_GROWTH,
> >                         decode(FUND_MASTER.SMALL_VALUE,to_char
> > (-9999),'N/A',FUND_MASTER.SMALL_VALUE) AS SMALL_VALUE,
> >                         FUND_MASTER.EXPENSE_RATIO,
> >                         row_number() over(order by EXPENSE_RATIO) as
> > r_exp
> >                  FROM MUTUAL_FUND,
> >                       FUND_MASTER,
> >                       FUND_RANK,
> >                       FUND_DAILY_PRICES
> >                  WHERE FUND_RANK.CURR_RANK in ".$rank."
> >                    AND FUND_MASTER.OBJ_DESCR = '".$arrgetcat[0][0]."'
> >                    AND MUTUAL_FUND.TICKER = FUND_RANK.TICKER
> >                    AND MUTUAL_FUND.TICKER = FUND_DAILY_PRICES.TICKER
> >                    AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
> >                    AND FUND_MASTER.MGR_START_DATE <> '-9999'
> >                    AND FUND_MASTER.EXPENSE_RATIO <> -9999
> >                    AND MUTUAL_FUND.TICKER <> '".$strTicker1."')
> > EXP_TICKERS
> >                WHERE r_exp <= 20) MGR_TICKERS,
> >                                   MUTUAL_FUND,
> >                                   FUND_MASTER
> >              WHERE MGR_TICKERS.TICKER = MUTUAL_FUND.TICKER
> >                AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID) a)
> >           YTD_TICKERS,
> >           MUTUAL_FUND,
> >           FUND_MASTER
> >         WHERE YTD_TICKERS.TICKER = MUTUAL_FUND.TICKER
> >           AND MUTUAL_FUND.M_FUND_ID = FUND_MASTER.FUND_ID
> >           AND m_asc <= 10) top4tickers
> >      WHERE ytd_desc <= 4";
>
> You make no mention of there being a valid output or performance
> issues with the code so if the code in fact produces the correct
> output and does so in a timely fashion why would want to change it?
>
> The fact that the query seems complicated may have more to do with the
> required result set and how the data is stored and the desire to
> return the data via a single set of queries rather than represent any
> flaw in the design of the SQL itself.
>
> IMHO -- Mark D Powell --

Biggest reason is that the place has a 'no hard coding of queries in PHP' rule. So, this needs to come out of PHP and go into a stored procedure..... Received on Mon Feb 09 2009 - 12:24:10 CST