Re: Online 7.11 Select Performance - Odd Results

Clay Irving writes: |> 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) This is perfectly reasonable, as the query can do an indexed read for both of these in order to return the ordered set. More importantly, it does not have to wait until everything is read before returning the first row. Did you time it to the last row or to the first row returned?? |> SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time) |> SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time) It is certainly reasonable that this would take longer, as it can no longer use the index to do the ordering, so it must read the entire table sequentially, and sort all the resulting data, and can't return the first row until the entire set has been sorted. Whether or not 10 minutes is reasonable for this is another matter, which gets into questions of tuning (most importantly, how much memory is available in the virtual segments, where sorting is done, versus the resident segment, where raw data is simply cached). |> SELECT column1,column, column3 ORDER BY 3 (works fine) Here you would be doing a key-only select, so again, the index provides your speedup. You might improve the performance of the two poor performers by reducing the number columns selected (which means more tuples can fit into the sort buffers). |> What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor |> HP K400, the query completes in approximately 10 minutes! This may well be understandable if you have a limited virtual memory in online, which would force the sort to be done in runs that have to be written out to disk. 22000 rows of 30 columns (how many bytes?) can chew up a whole lot of memory. |> 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... That, on the other hand, sounds like a bug. Dave Kosenko Disclaimer: All opinions expressed in this message are well-reasoned and insightful; needless to say, they are not those of Informix Software, its partners or lackeys. Anyone who says otherwise is itching for a fight. **************************************************************************** "I look back with some satisfaction on what an idiot I was when I was 25, but when I do that, I'm assuming I'm no longer an idiot." - Andy Rooney