How to speed up queries in the Informix database

Q: Why is it slow to operate on a table with 30,000 records and indexed tables?

A: 30,000 records are not too much, you feel a bit slow may be the reason for too many records, because each record is too long, or your machine performance is very low, some of the following methods may be useful for performance:

1. UPDATE STATISTICS (update satistics on tablename)

This is easy to do and is likely to lead to improved performance.

2. Delete

And then recreate the index. This can increase the continuity of the index. However, the requirement is DBA or have permission to create an index.

3.ALTER INDEX Idxname to CLUSTER

The purpose of this is to recreate the table and rearrange the records by index. The result is to force the record to be logically contiguous and to improve physical continuity. This is the most expensive, but the best effect. However, there can be only one cluster index in a table, and rearranging records may force other queries to use other indexes. For this kind of work, you have to have permission for DBA or ALTER TABLE. And note that when you run cluster index, you have to have enough space, because when you create cluster index for a table, all the records in the table are copied to a temporary table, sorted in a temporary table, and then the original table is deleted. Then change the name of the temporary table to the name of the original table. Therefore, if the data in this table varies greatly, such as the large number of insertions/deletions that are often performed, the benefits of doing so are not apparent.