Slooow select

Posted in 1998

Topics: Performance & Tuning, SQL Development & Query Writing

Michael Elizarov — Thu, 22 Jan 1998 10:37:53 +0300 — source: Informix-list mailing list archive (1991-1998)

--------------E71B400982205319F2A8C69D Content-Type: text/plain; charset=koi8-r Content-Transfer-Encoding: 7bit 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... 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 --------------E71B400982205319F2A8C69D Content-Type: text/html; charset=koi8-r Content-Transfer-Encoding: 7bit <HTML> 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... 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</HTML> --------------E71B400982205319F2A8C69D--