Re: Performance Issues: "order by"

Tom Bryan wrote: > > I've got Informix Online Workgroup Server 7.2 running under Solaris. > The DB will be serving some applications which require that the data > be provided in order based on one of the columns. > > I have a clustered index on that column. When I ran a test table > with an ESQL/C program, I was surprised that it took about > 12 seconds (when the machine was unloaded) during the open cursor > stage. The test table had 70,000 rows of about 55 bytes each. > > Using SET EXPLAIN, I see that the DB is doing a table scan since the > whole table must be returned, but it appears to be spending a lot of > effort ordering the query results into a temporary space. > > If I have a clustered index on this column, would the DB spend time > ordering the data anyway?! Why? Are there any other tricks I can > use to improve the DB performance when dealing with applications > which demand ordered results? > > P.S. Is there any good way to figure out how much time is being > spent just to read the data from disk versus the time spent doing > processing associated with the query? SET EXPLAIN output is rather > terse. Since the entire table has to be read anyway the optimizer has decided that the additional overhead of reading the index pages just to order the data is more than the cost of sorting the data. Since the table is clustered on the index that matches the order by the sort should be rather fast. Indeed 12 seconds to fetch 70000 rows sort them and write out the temp table is blindingly fast! If you want faster response for the first few rows you will have to upgrade to 7.24+ and SET OPTIMIZATION FIRST_ROWS which will tend to use the index. If you do that I think that you'll find that the OPEN returns immediately and that first few FETCHes returns immediately but that FETCHing all 70000 rows now takes more than twice as long as FETCHING the 70000 rows from the CURSOR using the default optimization (SET OPTIMIZATION ALL_ROWS). Art S. Kagel