IDS Forum

Re: Slow Query with index

Posted By: John Miller iii
Date: Tuesday, 10 May 2011, at 4:17 p.m.

In Response To: Re: Slow Query with index (Laurie Gustin)

Laurie:

You do not need a stored procedure. The following will work, used the
stores_demo database as an example schema.

select first 100 *
from customer
where lname >= "Miller" and (case when lname = 'Miller' and fname < "Jane"
then 'f' ELSE 't' END)::boolean
order by lname, fname

John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)

ids-bounces@iiug.org wrote on 05/10/2011 11:49:05 AM:

> [image removed]
>
> Re: Slow Query with index [23626]
>
> Laurie Gustin
>
> to:
>
> ids
>
> 05/10/2011 11:55 AM
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Please respond to ids
>
> Thanks for the insight Art!
>
> Still not knowing exactly why this queries performs differently, I took
what
> you said and put it all in a stored procedure that will separate the 'two

> sides of the union' and only do the second half if needed. It returns a
> sub-second response - no matter what part of the alphabet you are in.. or
how
> many records for a single last name.
>
> Thanks for everyone's help on this!!
>
> Laurie
>
> Laurie Gustin
> Database Administrator
> Dept of Technology Services
> Dept of Public Safety
> lgustin@utah.gov
> 801-965-4410
>
> >>> "Art Kagel" <art.kagel@gmail.com> 5/10/2011 11:17 AM >>>
> OK, here's the problem. The optimzer is treating the inequality filter
and
> the equality filters as if this were a UNION of two separate queries. So
it
> is seeing:
>
> select first 100
>
> vo_veh_id, vo_owner_type, vo_first_name, vo_last_name,
> vo_middle_name,
>
> vo_addr_set_id,vehownindex
> from vehicle_owner
> where ((vo_owner_type = 'O' OR vo_owner_type = 'E')
>
> and ((vo_last_name = 'WADSWORTH' and vo_first_name >= 'RALPH')))
> UNION
> select first 100
>
> vo_veh_id, vo_owner_type, vo_first_name, vo_last_name,
> vo_middle_name,
>
> vo_addr_set_id,vehownindex
> from vehicle_owner
> where ((vo_owner_type = 'O' OR vo_owner_type = 'E')
>
> and ((vo_last_name > 'WADSWORTH')))
> order by vo_last_name, vo_first_name;
>
> In order to join the two results sets, remove duplicates, and filter for
the
> first 100 rows, the optimizer must collect all of the matching rows from
> both queries and sort the results. I'm betting that 'ANDERSON' is either
> the smallest value in vo_last_name or there are more than 100 rows with
that
> value in the column and the optimizer knows that, so it is shortcutting
the
> query. It may even be eliminating the second part of the UNION or merging

> the two into a single '<=' comparison if you also eliminated the filter
on
> vo_first_name when you substituted 'ANDERSON'. I don't know because you
did
> not supply the query plan for that version of the query. If you eliminate

> the FIRST 100 clause, does it run faster for 'ROGER' & 'WADSWORTH'?
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> organization with which I am associated either explicitly, implicitly, or
by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Tue, May 10, 2011 at 12:32 PM, Laurie Gustin <lgustin@utah.gov> wrote:

>
> > Thanks for the suggestions...
> >
> > I changed the index, leaving out middle_name altogether and putting
type at
> > the end. Then ran the query without the index directive... below is the

> > query
> > plan. Unfortunately, I no longer have access to the old server to
compare
> > the
> > old query plan...
> >
> > QUERY: (OPTIMIZATION TIMESTAMP: 05-10-2011 10:22:31)
> > ------
> > select
> > first 100
> > vo_veh_id, vo_owner_type, vo_first_name, vo_last_name, vo_middle_name,
> > vo_addr_set_id,vehownindex
> > from vehicle_owner
> > where ((vo_owner_type = 'O' OR vo_owner_type = 'E') and
> > ((vo_last_name = 'WADSWORTH' and vo_first_name >= 'RALPH') or
(vo_last_name
> > >
> > 'WADSWORTH')))
> > order by vo_last_name, vo_first_name
> >
> > Estimated Cost: 751789
> > Estimated # of Rows Returned: 481854
> > Temporary Files Required For: Order By
> >
> > 1) informix.vehicle_owner: INDEX PATH
> >
> > (1) Index Name: informix.idx_vehowner
> >
> > Index Keys: vo_last_name vo_first_name vo_owner_type (Key-First)
(Serial,
> > fragments: ALL)
> >
> > Lower Index Filter: informix.vehicle_owner.vo_last_name > 'WADSWORTH'
> >
> > Index Key Filters: ((informix.vehicle_owner.vo_owner_type = 'O' OR
> > informix.vehicle_owner.vo_owner_type = 'E' ) )
> >
> > (2) Index Name: informix.idx_vehowner
> >
> > Index Keys: vo_last_name vo_first_name vo_owner_type (Key-First)
(Serial,
> > fragments: ALL)
> >
> > Lower Index Filter: (informix.vehicle_owner.vo_last_name = 'WADSWORTH'
AND
> > informix.vehicle_owner.vo_first_name >= 'RALPH' )
> >
> > Index Key Filters: ((informix.vehicle_owner.vo_owner_type = 'O' OR
> > informix.vehicle_owner.vo_owner_type = 'E' ) )
> >
> > The query still took over 2 minutes.
> >
> > When I run the query with the last name of ANDERSEN, it takes about 1
> > second...
> >
> > Thanks
> > Laurie
> >
> > >>> "Art Kagel" <art.kagel@gmail.com> 5/10/2011 9:13 AM >>>
> > Try adding this index and removing the +INDEX() optimizer directive:
> >
> > create index 'informix'.idx_vehowner on 'informix'.vehicle_owner
> > ( vo_last_name asc,
> > vo_first_name asc,
> > vo_owner_type,
> > vo_middle_name asc
> > ) ;
> >
> > If that doesn't work, post the original and new SET EXPLAIN outputs and

> > we'll take a look.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > Blog: http://informix-myview.blogspot.com/
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
opinions
> > and
> > do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other
> > organization with which I am associated either explicitly, implicitly,
or
> > by
> > inference. Neither do those opinions reflect those of other individuals

> > affiliated with any entity with which I am affiliated nor those of the
> > entities themselves.
> >
> > On Tue, May 10, 2011 at 11:00 AM, Laurie Gustin <lgustin@utah.gov>
wrote:
> >
> > > IDS 11.50 FC8X4
> > > Linux
> > >
> > > I have a query that has been successfully running on an IDS 10.0 for
> > quite
> > > some time. We recently moved the database to an 11.5 server, and the
> > result
> > > set appears different. For some reason the IDS 10 database seemed to
> > return
> > > the data in index order, even tho the index was not clustered. The
11.5
> > > database did not do that, so when we limited the number of rows
returned,
> > > often the rows we were looking for were not included in the result
set.
> > To
> > > fix
> > > this, I added the 'order by' statement. The result set is now as
> > expected,
> > > however, the query takes up to 2 minutes to complete. The index is as

> > > indicated.. and update statistics have been run using Art's dostats.
> > >
> > > select {+INDEX (idx_vehowner, vehicle_owner)}
> > > first 100
> > > vo_veh_id, vo_owner_type, vo_first_name, vo_last_name,
vo_middle_name,
> > > vo_addr_set_id,vehownindex
> > > from vehicle_owner
> > > where ((vo_owner_type = 'O' OR vo_owner_type = 'E') and
> > > ((vo_last_name = 'WILSON and vo_first_name >= 'RUDOLPH') or
(vo_last_name
> > >
> > > 'WILSON')))
> > > order by vo_last_name, vo_first_name;
> > >
> > > create index 'informix'.idx_vehowner on 'informix'.vehicle_owner
> > > ( vo_owner_type,
> > > vo_last_name asc,
> > > vo_first_name asc,
> > > vo_middle_name asc
> > > ) ;
> > >
> > > The really strange thing is that if I use a name beginning with 'A',
the
> > > query
> > > is screaming fast. The farther into the alphabet you go, the slower
it
> > gets
> > > -
> > > kind of like it is just scanning through the table in order?
> > >
> > > there are 7.8 million records in the table.
> > >
> > > Any suggestions would be helpful!
> > >
> > > Thanks!
> > > Laurie
> > >
> > > Laurie Gustin
> > > Database Administrator
> > > Dept of Technology Services
> > > Dept of Public Safety
> > > lgustin@utah.gov
> > > 801-965-4410
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
*******************************************************************************

> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --20cf3071d02e3dad8804a2ed66ea
> >
> >
> >
> >
>
>
*******************************************************************************

> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
*******************************************************************************

> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec54865d22a809b04a2ef21d1
>
>
>
*******************************************************************************

> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************

> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Slow Query with index
  Laurie Gustin -- Tuesday, 10 May 2011, at 11:00 a.m.
  • Re: Slow Query with index
    Keith Simmons -- Tuesday, 10 May 2011, at 11:11 a.m.
  • Re: Slow Query with index
    Art Kagel -- Tuesday, 10 May 2011, at 11:13 a.m.
    • Re: Slow Query with index
      Laurie Gustin -- Tuesday, 10 May 2011, at 12:32 p.m.
      • Re: Slow Query with index
        Jack Parker -- Tuesday, 10 May 2011, at 12:56 p.m.
        • Re: Slow Query with index
          Laurie Gustin -- Tuesday, 10 May 2011, at 1:14 p.m.
      • Re: Slow Query with index
        Jack Parker -- Tuesday, 10 May 2011, at 1:17 p.m.
      • Re: Slow Query with index
        Art Kagel -- Tuesday, 10 May 2011, at 1:17 p.m.
        • Re: Slow Query with index
          Laurie Gustin -- Tuesday, 10 May 2011, at 2:49 p.m.
          • Re: Slow Query with index
            John Miller iii -- Tuesday, 10 May 2011, at 4:17 p.m.
            • Re: Slow Query with index
              Art Kagel -- Tuesday, 10 May 2011, at 5:00 p.m.
            • Re: Slow Query with index
              Laurie Gustin -- Tuesday, 10 May 2011, at 5:13 p.m.
  • Re: Slow Query with index
    Jack Parker -- Tuesday, 10 May 2011, at 11:13 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.