Re: Help! Slow performance
In article <31CC9F21.3846@KandAsoft.com>,
Constantine Kozhukhin <cosk@KandAsoft.com> 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
The first asterisk in the 'matches "*ABC*"' conditions means that Informix
can't use an index to help with the query -- it has to perform a sequential
scan through each of your tables.
If you can say 'matches "ABC*"' instead, things will improve dramatically.
Cheers,
Kerry S
------------------------------------,------------------------------------------
Kerry Sainsbury, kerry@kcbbs.gen.nz | THE INFORMIX FAQ v2.5 June 96
Quanta Systems, Auckland | http://www.iiug.org/techinfo/faq/
New Zealand. Work: +64 9 377-4473 | ftp://ftp.iiug.org/pub/informix/faq
Home: +64 9 279-3571 | ftp://kcbbs.gen.nz:/pub/informix/