Re: Online 7.11 Select Performance - Odd Results

In <49iarh$sst@panix.com> clay@panix.com (Clay Irving) writes: > >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 Have you executed each query with the set explain on option? What does the explain results say is happening? I suspect that the first two queries are using the index and that last two are doing full table scans? Have you run onperf to see if you have high cpu usage and/or IO bound to a single disk? Is the table fragmented? Is the PDQ priority set to allow parallel scans? Good luck...