Re: Help! Slow performance
Billy Wheeler wrote:
>
> 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> ...Billy offers good suggestions, though instead of a temp table solution
you might try the UNION:
select product, division from products, divisions
where product matches "*ABC*"
and products.divno = divisions.divno
UNION
select product, division from products, divisions
where division matches "*ABC*"
and products.divno = divisions.divno
order by 1
Note that in UNION, you order by the reffered column number instead of a
column name. My experience has shown that UNION is significantly faster
than an OR statement that references different columns.
//////////////// =======================================================
////////// // Dennis J. Pimple Informix Software, Inc.
////// / /// Principal Consultant 5299 DTC Blvd Suite 740
///// // //// dennisp@informix.com Englewood CO 80111
//// // /////
/// // ////// recept: 303-850-0210
// // /////// direct: 303-740-5611 Opinions expressed are mine,
/ /////////// fax: 303-843-6408 and do not necessarily
//////////////// http://www.informix.com reflect those of my employer