IDS Forum

Re: Aggregates

Posted By: Fernando Nunes
Date: Tuesday, 24 January 2012, at 6:13 p.m.

In Response To: Aggregates (MURALI PAZHAYANNUR)

Very interesting situation.... But before I loose myself in my thoughts,
can you make sure you run proper (no offense) update stats on the tables
(something like Art's dostats). If not,can you change the queries?
I know that's not what you want to hear/read, but if it's possible I'd
advise you to do it at least as a workaround...
The change I propose is something to make the engine avoid using the
current indexes. For example apply the function DATE() to the date fields
in the sub-query equalities:

select

*
from

fi_debt_price dp
where

dp.settl_date = ( select

max(dpw.settl_date)

from

fi_debt_price dpw

where

dpw.nike_secrty_id = dp.nike_secrty_id and

DATE(dpw.settl_date) <= :settl_date(#(1/25/2012))

) and

dp.as_of_date = ( select

max(dpw.as_of_date)

from

fi_debt_price dpw

where

dpw.nike_secrty_id = dp.nike_secrty_id and

DATE(dpw.as_of_date) <= :as_of_date(#(1/20/2012))

)
and nike_secrty_id > 94000

Now, my thoughts:

I'm assuming this is a prepared query? and ":as_of_date(#(1/20/2012))" is a
form of expressing possible values?
If that's the case you're asking the engine to do something tricky: It must
calculate the query plan without knowing the values you will use. Don't get
me wrong... That's what happens in all prepared statements... But your case
has another tricky detail: You're using date/datetimes fields... and
apparently you're giving it values that are probably true for most cases.
<disclaimer on>
Yes, I have the feeling that the engine gives too much preference to
indexes with datetimes.
Yes, in a recent upgrade to 11.7 I got the feeling this has become more
evident
<disclaimer off>

Having said this, I did search for reported problems and what I found was
the opposite. This "trend" (in general, not in 11.7 specifically) was
recognized and some attempt to overcome it was created (but I believe it's
only scheduled for xC5). So, in short I found no facts that supported my
"feeling" that 11.7 was more sensitive to this than previous versions...
One thing you can try before running the stats:
onmode -wf USTLOW_SAMPLE=0

Finally, although I don't know the table schema and distributions, the most
effective indexes for that query would probably be:

nike_secrty_id, as_of_date DESC
and
nike_secrty_id, settl_date DESC

Naturally this is a quick guess that would require validation

On Tue, Jan 24, 2012 at 9:40 PM, MURALI PAZHAYANNUR <
pmurali@ftportfolios.com> wrote:

> Over the weekend we migrated production to v11.70.FC4 from 11.50.FC5W2 ( OS
> HP-UX 11.31 ia64).
>
> On v11.70 some queries are running slow ( minutes compared to 2 secs in
> v11.50). SET Explain does INDEX scans on both, though different indexes are
> used in the subselect with the aggregrate.
>
> The common thread is that these queries do aggregates. Here is a sample
> query:
>
> select * from fi_debt_price dp
> where dp.settl_date =
> (select max(dpw.settl_date)
> from fi_debt_price dpw
> where dpw.nike_secrty_id = dp.nike_secrty_id
> and dpw.settl_date <= :settl_date(#(1/25/2012)))
> and dp.as_of_date =
> (select max(dpw.as_of_date)
> from fi_debt_price dpw
> where dpw.nike_secrty_id = dp.nike_secrty_id
> and dpw.as_of_date <= :as_of_date(#(1/20/2012)))
> and nike_secrty_id > 94000
>
> SET EXPLAIN (v11.70):
>
> Subquery:
>
> ---------
>
> Estimated Cost: 291
>
> Estimated # of Rows Returned: 1
>
> 1) muralip.dpw: INDEX PATH
>
> (1) Index Name: dba.ev_debt_price_ix
>
> Index Keys: settl_date as_of_date nike_secrty_id (Key-Only) (Revers
> e) (Aggregate) (Serial, fragments: ALL)
>
> Lower Index Filter: muralip.dpw.settl_date <= 01/25/2012
>
> Index Key Filters: (muralip.dpw.nike_secrty_id = muralip.dp.nike_sec
> rty_id )
>
> Subquery:
>
> ---------
>
> Estimated Cost: 544
>
> Estimated # of Rows Returned: 1
>
> 1) muralip.dpw: INDEX PATH
>
> Filters: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id
>
> (1) Index Name: dba.ev_debt_price_as_of_ix
>
> Index Keys: as_of_date (Reverse) (Aggregate) (Serial, fragments: A
> LL)
>
> Lower Index Filter: muralip.dpw.as_of_date <= 01/20/2012
>
> SET EXPLAIN (v11.50):
>
> Subquery:
>
> ---------
>
> Estimated Cost: 717
>
> Estimated # of Rows Returned: 1
>
> 1) muralip.dpw: INDEX PATH
>
> Filters: muralip.dpw.settl_date <= 01/25/2012
>
> (1) Index Name: dba.ev_debt_price_secrty_ix
>
> Index Keys: nike_secrty_id (Serial, fragments: ALL)
>
> Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id
>
> Subquery:
>
> ---------
>
> Estimated Cost: 717
>
> Estimated # of Rows Returned: 1
>
> 1) muralip.dpw: INDEX PATH
>
> Filters: muralip.dpw.as_of_date <= 01/20/2012
>
> (1) Index Name: dba.ev_debt_price_secrty_ix
>
> Index Keys: nike_secrty_id (Serial, fragments: ALL)
>
> Lower Index Filter: muralip.dpw.nike_secrty_id = muralip.dp.nike_secrty_id
>
> I have tried these simple steps:
>
> 1. Upd stats high on table, indexes; low on table drop distributions
> 2. Drop-reload table, stats
> 3. onmode -wf DS_NONPDQ_QUERY_MEM>= 25% DS_TOTAL_MEMORY
>
> Not sure what to try next.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--485b397dd69585b42e04b74e4bf0

Messages In This Thread

• Aggregates
  MURALI PAZHAYANNUR -- Tuesday, 24 January 2012, at 4:40 p.m.
  • Re: Aggregates
    Art Kagel -- Tuesday, 24 January 2012, at 5:31 p.m.
  • Re: Aggregates
    Fernando Nunes -- Tuesday, 24 January 2012, at 6:13 p.m.
  • Re: Aggregates
    MURALI PAZHAYANNUR -- Thursday, 26 January 2012, at 1:22 p.m.
    • Re: Aggregates
      Fernando Nunes -- Thursday, 26 January 2012, at 2:06 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.