Re: Help! Slow performance
Matches is your first killer!!! Index is NOT being used, if you can change
it to "ABC*" okay.
Create index someidx on products(divno);Update Stats on both tables! Then run this:
select product, division
from products, divisions
where products.divno = divisions.divno
and ( product matches "*ABC*"
or division matches "*ABC*")
order by productMay not make much difference, yet you should see that the index for divno is
being used, sequential scan on prodcut and division because of the matches
"*ABC*".
Also you may try seperating columns in you index, could not tell if you
had one index with both columns or two seperate indexes. Use the explain
to see if the cost changes.....MY DBA words of the month "EXPLAIN,
EXPLAIN, EXPLAIN". Let me know what you get to work for you and hope
I have been of some help!
-------------------------------------------------------------------------
Cheryl Kendricks
Internet:cherylk@prod1.jcdc.doleta.gov OR cherylk@gwysmtp.jcdc.doleta.gov
DTSI, Inc.
Database Administrator - DOL Job Corps San Marcos, Texas
------------------------------------------------------------------------
On Sat, 22 Jun 1996, Constantine Kozhukhin wrote:
> Can somebody help, please?
> I have a performance problem with Informix 5.03 on SUN OS 4.1.4.
> Queries like the following are running 20-25 seconds on SPARC 4. This is
> unacceptably slow for our client production environments.
>
> Table: Colomn: Index: Number of rows:
> products prodno prodno 30000
> product product
> divno
> company
> category
>
> division divno divno 11000
> division division
> company
>
>
> Query:
> select product, division from products, divisions
> where (product matches "*ABC*" or division matches "*ABC*")
> and products.divno = divisions.divno
> order by product>
> Making product index a cluster index did not help.
>
> Thanks.
>
> Julie Kozhukhin (juliek@KandAsoft.com)
>