Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - SQL Usage » Query Processing » Query optimization and execution » Improving query performance » Indexes

 

Improving index performance

If your index is not performing as expected, you may want to consider the following actions:

These measures are aimed at increasing index selectivity and index fan-out, as explained below.

Index selectivity

Index selectivity refers to the ability of an index to locate a desired index entry without having to read additional data.

If selectivity is low, additional information must be retrieved from the table page that the index references. These retrievals are called full compares, and they have a negative effect on index performance.

The FullCompare property function keeps track of the number of full compares that have occurred. You can also monitor this statistic using the Sybase Central Performance monitor or the Windows Performance Monitor.

Note

The Windows Performance Monitor may not be available on Windows Mobile.

In addition, the number of full compares is provided in the graphical plan with statistics. For more information, see Common statistics used in the plan.

For more information about the FullCompare function, see Database properties.

Index structure and index fan-out

Indexes are organized in several levels, like a tree. The first page of an index, called the root page, branches into one or more pages at the next level, and each of those pages branch again, until the lowest level of the index is reached. These lowest level index pages are called leaf pages. To locate a specific row, an index with n levels requires n reads for index pages and one read for the data page containing the actual row. In general, fewer than n reads from disk are needed, since index pages that are used frequently tend to be stored in cache.

The index fan-out is the number of index entries stored on a page. An index with a higher fan-out may have fewer levels than an index with a lower fan-out. Therefore, higher index fan-out generally means better index performance. Choosing the correct page size for your database can improve index fan-out. See Table and page sizes.

You can see the number of levels in an index by using the sa_index_levels system procedure. See sa_index_levels system procedure.


Composite indexes