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

SAP Sybase SQL Anywhere 16.0 » UltraLite - Database Management and Reference » UltraLite performance tips » Query performance tips


Index scan creation and maintenance

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. Indexes can greatly improve the performance of searches on the indexed column(s) when used strategically.

Use the following recommended practices for improving query performance:

  • Create an index on any column:

    • for values that you search for on a regular basis
    • that the query uses to join tables
    • that are commonly used in ORDER BY, GROUP BY, or WHERE clauses
  • Create a composite index and ensure that the first column of the index is used most often by the predicate in your query when creating it.

  • Ensure that the update maintenance overhead an index introduces is not too high for the memory of your device.

  • Do not create or maintain unnecessary indexes. Indexes must be updated when the data in a column is modified, so all insert, update, and delete operations are performed on the indexes as well.

  • Create an index on large tables.

  • Do not create redundant indexes. For example, if you create an index on table T with columns (x, y), you can create a redundancy if there is another existing index on T with columns (x, y, z).

 See also