Re: Help! Slow performance
At 09:34 PM 6/22/96 -0400, Constantine Kozhukhin wrote:
>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
A couple of thoughts:
1. A WHERE clause using an asterisk to start of with is probably the cause
of your performance problems. So try
select product, division
from products, divisions
where (product matches "[More specific]ABC*"
or division matches "[More specific]ABC*")
and products.divno = divisions.divno
order by product
But if you can get rid of the "*ABC*" approach, it will almost certainly
sort it out.
2. The ORDER BY could be slowing the whole thing down, but not as much as
the WHERE clause.
3. You might try:
SELECT product, divno
FROM products
WHERE (product MATCHES "*ABC*"
OR divno IN (SELECT divno FROM divisions WHERE division matches "*ABC*"))
INTO TEMP temp1;
SELECT division, divno
FROM divisions
WHERE division MATCHES "*ABC*"
INTO temp2;
SELECT product, division
FROM temp1, temp2
WHERE temp1.divno = temp2.divno
ORDER BY 1;
4. You must have an index on products.divno and an index on divisions.divno.
Point number 1 is almost certainly _it_ though.
HTH.
Hasta la vista!
--
Billy Wheeler -- Director, The West Solutions Group
(E) billy@west.co.za (W) +27 11 803 2151 (F) +27 11 803 2189
(C) +27 83 250 2324 (H) Forget it, my wife will kill me! :-)
"...but apart from that, Mrs Kennedy, how was the parade?"