RE: Help! Slow performance

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. Contact your mail administrator for information about upgrading your reader to a version that supports MIME. ------ =_NextPart_000_01BB61C0.5B7D1600 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Dennis is right that the union instead of the or clause would help a great deal. One other problem you have is the following '... matches "*<text>"...'. This matches clause will force a sequential scan of this column, and even worse yet what informix will do is join the tables first and then do a sequential scan of the join table. The only solution is to avoid the matches clause with the wild card at the begining of the string. Hope this helps.... __________________________________________ Russell P. Kahler russell@oncontact.com (414)375-6540 W67 N250A Evergreen Blvd. Cedarburg, Wi 53012 ___________________________________________ }---------- }From: Dennis J Pimple[SMTP:dennisp@informix.com] }Sent: Sunday, June 23, 1996 10:52 AM }To: informix-list@rmy.emory.edu }Subject: 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 } ------ =_NextPart_000_01BB61C0.5B7D1600--