Re: Please help optimize a query:
First cut:
1. Update statistics to the recommended levels (see the Performance Guide
and John Miller III's white paper)
2. Save SET EXPLAIN output for this original query and all subsequent
attempts to optimize it.
3. Why are you forcing the use of a particular index? Let the optimizer
do its job!
Next:
- Try the query again without the optimizer directives. If that doesn't
work, try:
- Add an index as follow
create index ix_sr4 on status_records( sub_min, canc_dt, trandt ); -- Ifthis is a unique key then make it a unique index
- If that doesn't work, select the data for the correlated sub-queries
into a temp table and use a simple join to the temp table to get the
matches:
select sub_min, max(canc_dt) as canc_dt_max, max(trandt) as trandt_max
from status_records y
where canc_dt >= '08/04/2008'
and canc_dt < '09/04/2008'
and sub_min = s.sub_min
group by sub_min
into temp subquery;
select
s.trandt
,s.sub_min as min
,s.s_class
,s.status
,s.active_dt
,s.exp_dt
,s.canc_dt
from status_records s, subquery q
where s.canc_dt >= '08/04/2008'
and s.canc_dt < '09/04/2008'
and s.canc_dt = q.canc_dt_max
and s.trandt = q.trandt_max;
Worst case, post the SET EXPLAIN output for the various versions of the
query.
Art
On Wed, Aug 6, 2008 at 9:37 AM, SAIRA VARGHESE <svarghese@locus.net> wrote:
> //QUERY IS AS FOLLOWS:
> select --+index (s ix_sr2)
>
> s.trandt
>
> ,sub_min as min
>
> ,s.s_class
>
> ,s.status
>
> ,s.active_dt
>
> ,s.exp_dt
>
> ,s.canc_dt
>
> from status_records s
>
> where s.canc_dt >= '08/04/2008'
>
> and s.canc_dt < '09/04/2008'
>
> and s.canc_dt = (select --+index(y ix_sr2)
>
> max(canc_dt)
>
> from status_records y
>
> where canc_dt >= '08/04/2008'
>
> and canc_dt < '09/04/2008'
>
> and sub_min = s.sub_min)
>
> and s.trandt = (select --+index(y ix_sr2)
>
> max(trandt)
>
> from status_records y
>
> where canc_dt >= '08/04/2008'
>
> and canc_dt < '09/04/2008'
>
> and sub_min = s.sub_min)
>
> //INFO...
> I use Informix 10.00 ;
> Row counts = Status records table 541,791;
>
> Indexes = on status_records, there are 3 indexes -
>
> create unique index "systems".ix_sr on "systems".status_records
>
> (sub_min,canc_dt,s_class,status,active_dt,exp_dt) using btree;
>
> create index "systems".ix_sr2 on "systems".status_records (canc_dt,sub_min)
> using btree ;
> create index "systems".ix_sr3 on "systems".status_records (trandt) using
> btree
> ;
>
> Could someone please help me optimize this query?
>
> thanks.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Two problems. First the sub_min = s.sub_min filter doesn't belong in the
query to temp table (forgot to delete that line), and second:
I think that you may have a logic problem with this query (including your
original). You are finding canceled transactions with the newest
transaction date and the newest cancel date. What if the most recently
canceled transaction isn't the newest transaction that was canceled, then
you will not see any records for that sub_min! I converted what you had
faithfully, but I realized just now that your original query may be
semantically pathological.
The correct query may be to match the latest transaction date ONLY from
among the records with the latest cancellation date, which may require
another join or sub-query..
select sub_min, max(canc_dt) as canc_dt_max, trandt
from status_records y
where canc_dt >= '08/04/2008'
and canc_dt < '09/04/2008'
group by sub_min
into temp subquery;
Then in the main query you select joining canc_dt to q.canc_dt_max and and
s.trandt = MAX(subquery.trandt).
Art
On Wed, Aug 6, 2008 at 10:09 AM, Art Kagel <art.kagel@gmail.com> wrote:
> First cut:
>
> 1. Update statistics to the recommended levels (see the Performance Guide
>
> and John Miller III's white paper)
>
> 2. Save SET EXPLAIN output for this original query and all subsequent
>
> attempts to optimize it.
>
> 3. Why are you forcing the use of a particular index? Let the optimizer
>
> do its job!
>
> Next:
>
> - Try the query again without the optimizer directives. If that doesn't
>
> work, try:
>
> - Add an index as follow
>
> create index ix_sr4 on status_records( sub_min, canc_dt, trandt ); -- If> this is a unique key then make it a unique index
>
> - If that doesn't work, select the data for the correlated sub-queries
>
> into a temp table and use a simple join to the temp table to get the
>
> matches:
>
> select sub_min, max(canc_dt) as canc_dt_max, max(trandt) as trandt_max
> from status_records y
> where canc_dt >= '08/04/2008'
> and canc_dt < '09/04/2008'
> and sub_min = s.sub_min
> group by sub_min
> into temp subquery;>
> select
> s.trandt
> ,s.sub_min as min
> ,s.s_class
> ,s.status
> ,s.active_dt
> ,s.exp_dt
> ,s.canc_dt
> from status_records s, subquery q
> where s.canc_dt >= '08/04/2008'
> and s.canc_dt < '09/04/2008'
> and s.canc_dt = q.canc_dt_max
> and s.trandt = q.trandt_max;
>
> Worst case, post the SET EXPLAIN output for the various versions of the
> query.
>
> Art
>
> On Wed, Aug 6, 2008 at 9:37 AM, SAIRA VARGHESE <svarghese@locus.net>
> wrote:
>
> > //QUERY IS AS FOLLOWS:
> > select --+index (s ix_sr2)
> >
> > s.trandt
> >
> > ,sub_min as min
> >
> > ,s.s_class
> >
> > ,s.status
> >
> > ,s.active_dt
> >
> > ,s.exp_dt
> >
> > ,s.canc_dt
> >
> > from status_records s
> >
> > where s.canc_dt >= '08/04/2008'
> >
> > and s.canc_dt < '09/04/2008'
> >
> > and s.canc_dt = (select --+index(y ix_sr2)
> >
> > max(canc_dt)
> >
> > from status_records y
> >
> > where canc_dt >= '08/04/2008'
> >
> > and canc_dt < '09/04/2008'
> >
> > and sub_min = s.sub_min)
> >
> > and s.trandt = (select --+index(y ix_sr2)
> >
> > max(trandt)
> >
> > from status_records y
> >
> > where canc_dt >= '08/04/2008'
> >
> > and canc_dt < '09/04/2008'
> >
> > and sub_min = s.sub_min)
> >
> > //INFO...
> > I use Informix 10.00 ;
> > Row counts = Status records table 541,791;
> >
> > Indexes = on status_records, there are 3 indexes -
> >
> > create unique index "systems".ix_sr on "systems".status_records
> >
> > (sub_min,canc_dt,s_class,status,active_dt,exp_dt) using btree;
> >
> > create index "systems".ix_sr2 on "systems".status_records
> (canc_dt,sub_min)
> > using btree ;
> > create index "systems".ix_sr3 on "systems".status_records (trandt) using
> > btree
> > ;
> >
> > Could someone please help me optimize this query?
> >
> > thanks.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those
> opinions reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.