Most of a query’s execution time is spent reading data pages from disk. Therefore, most performance improvement comes from reducing the number of disk reads needed for each query.
When a query performs a table scan, Adaptive Server reads every page in the table because no indexes are available to help it retrieve the data. The query may have poor response time, because disk reads take time. Queries that incur costly table scans also affect the performance of other queries on your server.
Table scans can increase the time other users have to wait for a response, since they use system resources such as CPU time, disk I/O, and network capacity.
Table scans use a large number of disk reads (I/Os) for a given query. When you have become familiar with the access methods, tuning tools, the size and structure of your tables, and the queries in your applications, you should be able to estimate the number of I/O operations a given join or select operation will perform, given the indexes that are available.
If you know what the indexed columns on your tables are, and the table and index sizes, you can often look at a query and predict its behavior. For different queries on the same table, you might be able to draw these conclusions:
This query returns a single row or a small number of rows that match the where clause condition.
The condition in the where clause is indexed; it should perform two to four I/Os on the index and one more to read the correct data page.
All columns in the select list and where clause for this query are included in a nonclustered index. This query will probably perform a scan on the leaf level of the index, about 600 pages.
Adding an unindexed column to the select list would force the query to scan the table, which would require 5000 disk reads.
No useful indexes are available for this query; it is going to do a table scan, requiring at least 5000 disk reads.
This chapter describes how tables are stored, and how access to data rows takes place when indexes are not being used.
Chapter 5, “Indexes,” in Performance and Tuning Series: Locking and Concurrency Control describes access methods for indexes. Chapter 3, “Setting Space Management Properties” and Chapter 4, “Table and Index Size” explain how to determine which access method is being used for a query, the size of the tables and indexes, and the amount of I/O a query performs. These chapters provide a basis for understanding how the optimizer models the cost of accessing the data for your queries.
