Re: Online 7.11 Select Performance - Odd Results

For those two no index is being used : SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time) SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time) Temp table has to be created for the order by , run the set explain. When doing an order by on a large selection , there should be an index coresponding for that order by. The only reason this one works well is that you selecting the 3 index columns , so only the index is being read. SELECT column1,column, column3 ORDER BY 3 (works fine) Mariusz Malogrosz mariuszm@tecsys.com Clay Irving (clay@panix.com) wrote: : I seem to remember reading something a week or so ago about some : strange performance problems in Online 7.1x select. Supposedly, : the problems were fixed in subsequent releases. Maybe so, but it looks : like we found another problem. So far, Informix Tech Support is stumped : (even though the problem is easily and dependably reproduceable). : This is the problem: : We have INFORMIX-Online Dynamic Server 7.11.UC1 running on : HP-UX v.10.01. : table_x (30 columns, 22000 rows), index on (column1,field2,field3) : SELECT * FROM table_x ORDER BY 1,2,3 (works fine) : SELECT * FROM table_x ORDER BY 1 (works fine) : SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time) : SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time) : SELECT column1,column, column3 ORDER BY 3 (works fine) : What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor : HP K400, the query completes in approximately 10 minutes! : Even wierder: Move these queries into a distributed environment and : the *EXTREMELY* long query completely shuts down the TCP/IP pipe : between two systems -- Users can rlogin to the database server... : Has anyone seen anything like this, and most importantly, does anyone : have any suggestions? : OBComment: We already took aspirin. : -- : - o - o Clay Irving (clay@panix.com), N2VKG : o - o o AEC NYC ARES, New York County : o - Deputy Radio Operator, NYC RACES, New York County : - o - - Start --> http://www.panix.com/clay