ASA SQL User's Guide
Monitoring and Improving Performance
Top performance tips
When executing a query, Adaptive Server Anywhere chooses how to access each table. Indexes greatly speed up the access. When the database server cannot find a suitable index, it instead resorts to scanning the table sequentially—a process that can take a long time.
For example, suppose you need to search a large database for people, but you only know either their first or their last name, but not both. If no index exists, Adaptive Sever Anywhere scans the entire table. If however, you created two indexes (one that contains the last names first, and a second that contains the first names first), Adaptive Sever Anywhere scans the indexes first, and can generally return the information to you faster.
Although indexes let Adaptive Server Anywhere locate information very efficiently, exercise some caution when adding them. Each index creates extra work every time you insert, delete, or update a row because Adaptive Server Anywhere must also update all affected indexes.
Consider adding an index when it will allow Adaptive Server Anywhere to access data more efficiently. In particular, add an index when it eliminates unnecessarily accessing a large table sequentially. If, however, you need better performance when you add rows to a table, and finding information quickly is not an issue, use as few indexes as possible.
You may wish to use the Index Consultant to guide you through the selection of an effective set of indexes for your database. For more information, see Index Consultant overview.
Using clustered indexes stores rows in a table in approximately the same order as they appear in the index.
For more information, see Indexes and Using clustered indexes.