Performance Issues: "order by"
Posted in 1998
Topics: Performance & Tuning, Connectivity: ESQL/C, 4GL & Embedded SQL, Migration, Import/Export & Data Conversion, Platform-Specific Issues, Clustering, Grid & MACH11
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. Thanks. ---Tom