Re: Slooow select

In article <34C6F750.D4EEB8C2@abgcard.msk.ru>, Michael Elizarov <michael.elizarov@abgcard.msk.ru> writes >Hello *! >I have a table of ranges, which looks like > >create table range ( > low decimal(19), > hi decimal(19) check (hi > low), > ..... >);> >The table size is ~50K recs. Because of not shown part of the table I >can't have a primary key on low or hi. But I have indexes on them. Given >a particular value I need to make a select in range: > >select * from range where value between lo and hi> >All this runs (pls, do not laugh -- it is a test site) on a 486/66, >32RAM, SCO 3.2, Ifx WGS 7.20. A select with small values runs instantly. >When the value raises, and gets up to the highest values of hi and >further, select takes 5..10 sec. >I set explain on, and it showed that from some critical value (~ in the >middle of ranges) select is done by sequential scanning, not by index. >Ok, I set unique indexes on lo and hi (the particular data allowed >this), exported OPTCOMPIND=0 (only consider the index path in a join >pair) and run the select again. sqlexplain.out showed that select was >done by index only, BUT THE TIME DID NOT CHANGED... > Run the query both ways in in dbaccess. When it finishes, switch to another session and run onstat -u. Does the number of reads and writes for your session change? >The real system will run on a dual-Pentium, with 128 RAM, but with table >size ~250-300K. Can I do something except adding more RAM/CPU? > >TIA ? regards, >Mike -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care