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

SQL Anywhere 10.0.1 » UltraLite - Database Management and Reference » UltraLite Performance and Optimization » Optimizing UltraLite query performance

Optimizing UltraLite query performance Next Page

Using index scans

An index is a set of pointers to rows of a table, based on the order of the values of data in one or more table columns. An index is a database object. The index is maintained automatically by UltraLite after it has been created. You can create one or more indexes to improve the performance of your queries, or, depending on the type of index you create, to ensure that row values remain unique.

An index provides an ordering of a table's rows based on the values in some or all of the columns. When creating indexes, the order in which you select columns to be indexed becomes the order in which the columns actually appear in the index. Consequently, when you use them strategically, indexes can greatly improve the performance of searches on the indexed column(s).

UltraLite supports the following indexes. These indexes can be single or multi-column (also known as composite indexes). You cannot index LONG VARCHAR or LONG BINARY columns.

Primary keyRequired. An instance of a unique key. You can only have one primary key. Values in the indexed column or columns must be unique and cannot be NULL.
Foreign key1Optional. Values in the indexed column or columns can be duplicated. Nullability depends on whether the column was created to allow NULL. Values in the foreign key columns must exist in the table being referenced
Unique key2Optional. Values in the indexed column or columns must be unique and cannot be NULL.
Non-unique indexOptional. Values in the indexed column or columns can be duplicated and can be NULL.
Unique indexOptional. Values in the indexed column or columns cannot be duplicated and can be NULL.

1A foreign key can reference either a primary key or a unique key.

2Also known as a unique constraint.

Performance tips
Determining the access method used by the optimizer

The UltraLite optimizer uses sophisticated optimization strategies when choosing an index for query optimization. Except in the case of simple queries, you cannot easily predetermine which index the optimizer uses to optimize the query performance, or if an index is used at all. As the complexity increases, which index is selected depends on what clauses are required by your query. In some cases, the presence of a FOR READ ONLY clause may cause the optimizer to choose a direct table scan instead of an index to yield better query performance.

When optimizing a query, the optimizer looks at the requirements of the query and checks if there are any indexes that it can use to improve performance. If performance cannot be improved with any index, then the optimizer does not scan one: either a temporary table or a direct page scan is used instead. Therefore, you may need to experiment with your indexes and frequently check the generated query plans to ensure that:

For complex queries, knowing which index is used is even less predictable. For example, when a query contains a WHERE predicate, and a GROUP BY clause in addition to an ORDER BY clause, one index alone might not satisfy the search conditions of this query. So if you have created an index to meet the selectivity requirements of the WHERE predicate, you may find that the optimizer does not, in fact, use it. Instead, the optimizer may use an index that offers better performance for the ORDER BY conditions, because this clause could require the most processing.

Checking the query plan

You can check the query plan either programmatically with the appropriate API call or on the Plan tab in Interactive SQL:

See also

Tuning query performance with index hashing
Choosing an optimal hash size