Re: The Query From Hell

From: joel garry <joel-garry_at_home.com>
Date: Mon, 9 Feb 2009 10:51:33 -0800 (PST)
Message-ID: <cf9bf23f-6e00-4f00-b440-52ff7f6b6d34_at_t39g2000prh.googlegroups.com>

On Feb 9, 9:30 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 9, 10:36 am, Mtek <m..._at_mtekusa.com> wrote:
>

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

> >                  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."')
>
> 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?

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.

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

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

--
_at_home.com is bogus.
http://www.azstarnet.com/sn/hourlyupdate/278448.php

Received on Mon Feb 09 2009 - 12:51:33 CST