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. May be a clue here "the open cursor stage". If you have a scroll cursor the table will be copied. Also, I suspect that the SET ISOLATION level may have an effect here (anyone else know about this?). Try setting it lower for this transaction. > > 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. > > Thanks. > ---Tom -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 Mail: Peter.Lancashire.PL1@bayer.co.uk --- My Internet plumbing does not allow me to mail and post news together. Sorry. All opinions are my own and not those of Bayer plc. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/