Informix query slow

IDS 9.04 on unix.

I got a table which has 200000+ rows ,each row has 200+ columns. when I execute a query (supposed to return 470+ rows with 50 columns)on this table,it takes 100+ secs to return,and dbvisualizer told me :

eexecution time : 4.87 secs fetch time : 97.56 secs

if I export all the 470+ rows into a file, the file size will less than 800K

UPDATE STATISTICS has been runned,only 50 columns selected,no blob involved 开发者_运维知识库,if I select first 100 rows ,it only need 5 secs to return.

Plz help !

If SELECT FIRST 100 only takes a few seconds, it suggests that the query-plan for FIRST_ROWS is dramatically different to that for ALL_ROWS.

Try running the query with SET EXPLAIN ON; both with and without the FIRST n. It might give you a clue what's going on.

Use:

set explain on avoid_execute;

YOUR_QUERY

set explain off;

And review the sqexplain.out file in your folder.