Re: forcing index use with OWS/NT

mjgator@sibretown.com wrote: > > I am mystified. Running Online Workgroup Server on NT. D4GL front end. > Have a table, POLICY, with 15 or so columns. Unique index on Polno (the > policy #), duplicate index on polsetup (date) and polname (Char 35). Have a > QBE screen set up. If client enters SMI* in the policy name field (polname) > and queries, it does a brute force sequential search...OR uses the polno > index (according to set explain). I have tried dropping the polname index > and making a composite of polname/polno...and then one of polno/polname. > Nada. The select statement does an ORDER BY POLNO since even though I want > all the names SMI*, I want them delivered in policy order. Is this my > problem? When I try this in DBACCESS with no ordering, etc, it ignores the > polname index. > > Originally this identical query/screens/table was on old I4GL on SE and worked > perfectly. I hope you remembered the first three rules of performance tuning: 1) UPDATE STATISTICS 2) UPDATE STATISTICS 3) UPDATE STATISTICS :-) The ORDER BY is a factor in optimisation. Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+