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://URL HERE?t='||
MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.T ICKER||'</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://URL HERE?t='||
MUTUAL_FUND.TICKER||'&type=main\">'||MUTUAL_FUND.T ICKER||'</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://URL HERE?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";

On Feb 9, 9:36*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
My personal field guide to digital archeology:

1) 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.

2) 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.

3) Remove all of the subqueries and see if they run independently.
What do they tell you about what's going on?

4) 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.

5) 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

On Feb 9, 10:36*am, Mtek <m... (AT) mtekusa (DOT) com> wrote:
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 --

On Feb 9, 11:30*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
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.....

On Feb 9, 9:30*am, Mark D Powell <Mark.Pow... (AT) eds (DOT) com> wrote:
Think on these two parts I've left in the snippage. How is the
optimizer going to deal with -9999, especially the date in an
analytic? Is it really going to skip the query when the value is
blank?

In the stuff I've clipped, I have no idea if this type of analytics
usage will be better than other ways, and am not entirely certain
there is any way to predict it. Analytics may rock and roll, except
when they roll over and fall asleep.

That's a legitimate point, but on the face of it, to me anyways, it
looks like both a maintenance nightmare and performance problems will
come sooner rather than later.

The maintenance programming is often underplayed, but often the most
expensive part. This looks like some stock ticker web page comparing
year to date results of various funds, where any problems will be very
visible to end users.

jg
--
@home.com is bogus.
http://www.azstarnet.com/sn/hourlyupdate/278448.php

Mtek schreef:

Shakespeare