Answer time failure in Select.

We have a table of customs which have 7000 records. The last code_custom = is 7000. If we have a query with a where clausule "code_custom < 74000" in a cursor = it run well. ( between 2 and 3 seconds to go from first to last record ) Nevertheless if the where clausule is "code_custom < 75000" it need = between 25 and 35 seconds to do it. select code_custom, cli_busqueda from com_custom where code_custom < 74000 order by cli_busqueda ( 2 seconds ) select code_custom, cli_busqueda from com_custom where code_custom < 75000 order by cli_busqueda ( 30 seconds ) =20Index 1: code_custom Index 2: cli_busqueda ?????? Sorry my english. Thank You. jhernandez@geuropa.com=20

When you change filter bounds, you are changing filter's selectivity. That's why optimizer, based on it's statistics, decide to scan table sequentially, avoiding index path. You need UPDATE STATISTICS HIGH for columns, that head an index. -- With best regards, Yuri Dovgart, SAP R/3, Informix consultant, "Telecominvest" company. E-mail y_dovgart@tci.ukrtel.net > > We have a table of customs which have 7000 records. The last code_custom = > is 7000. > If we have a query with a where clausule "code_custom < 74000" in a cursor = > it run well. ( between 2 and 3 seconds to go from first to last record ) > Nevertheless if the where clausule is "code_custom < 75000" it need = > between 25 and 35 seconds to do it. > > select code_custom, cli_busqueda from com_custom > where code_custom < 74000 > order by cli_busqueda > ( 2 seconds )> > select code_custom, cli_busqueda from com_custom > where code_custom < 75000 > order by cli_busqueda > ( 30 seconds ) > =20> Index 1: code_custom > Index 2: cli_busqueda > > ?????? > > Sorry my english. > > Thank You. > > jhernandez@geuropa.com=20 > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Juan Manuel Hernandez del Olmo wrote: > > We have a table of customs which have 7000 records. The last code_custom = > is 7000. > If we have a query with a where clausule "code_custom < 74000" in a cursor = > it run well. ( between 2 and 3 seconds to go from first to last record ) > Nevertheless if the where clausule is "code_custom < 75000" it need = > between 25 and 35 seconds to do it. > > select code_custom, cli_busqueda from com_custom > where code_custom < 74000 > order by cli_busqueda > ( 2 seconds )> > select code_custom, cli_busqueda from com_custom > where code_custom < 75000 > order by cli_busqueda > ( 30 seconds ) > =20> Index 1: code_custom > Index 2: cli_busqueda > > ?????? I'd guess that the table once help other values and that you have not updated statistics properly. I assume that there is an index on code_custom!?! If so it appears the first query is performing an indexes lookup of code_custom and the second is performing a table scan or worse an indexed lookup on cli_busqueda to satisfy the ORDER BY clause. Run both with SET EXPLAIN ON; and verify the cause of the behavior then run a full set of UPDATE STATISTICS commands on the table and its indexes (you can use my dostats.ec utility or work out the proper set of commands by reading the 7.2 release notes). Art S. Kagel