Re: The Query From Hell
Date: Mon, 9 Feb 2009 08:16:29 -0800 (PST)
Message-ID: <e3e3fe8c-adb8-411c-a15b-ffac9165ce2a_at_v5g2000prm.googlegroups.com>
On Feb 9, 9: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......
My personal field guide to digital archeology:
- List all of the source tables referenced in the query. It may be helpful to copy the structure to Excel, where you can run your fingers through the meta data. If the table is well designed there should be comments explaining what's what.
- Look at any documentation that you provided the consultants and that they gave you. If not explicit, reconstruct the logic they used. There may be some cultural issues here as well. For example, if they repurposed everyone in the shop from doing hierarchical dbs to SQL you may see some normalization, ah, issues, because they don't think in terms of one-to-many.
- Remove all of the subqueries and see if they run independently. What do they tell you about what's going on?
- Look at the dialect used. For example, your code is full of Decode statements. Some people consider decode to be a very sloppy way to work. That's a matter of opinion, but it certainly gives you further insight into what they were thinking.
- Ask the consultant - if they're still around. An hour or two of poking around and then talking to the contractor can lead you to the that "ah ha" moment where you understand the approach used and can then decide whether to continue or rewrite.
Good luck, HTH
Tim Mills-Groninger
>
> 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";
Received on Mon Feb 09 2009 - 10:16:29 CST