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 » Using index scans

Tuning query performance with index hashing Next Page

Choosing an optimal hash size


The UltraLite default maximum hash size of 4 bytes was carefully chosen to suit most deployments. You can increase the size to include more data with the row ID; however, this change could increase the size of the index and fragment it among multiple pages, which can in turn possibly increase the size of the database as a result. The impact of an increased maximum hash size depends on the number of rows in the table: for example, if you only have a few rows, a large index hash key would still fit on the index page. No index fragmentation occurs in this case

When choosing an optimal hash size, consider the data type, the row data, and the database size (especially if a table contains many rows) as described in the following sections. The only way to determine if you have chosen an optimal hash size is to run benchmark tests against your UltraLite client application on the target device. You need to observe how various hash sizes affect the application and query performance, in addition to the changes in database size itself.

The data type

If you want to hash the entire value in a column, note the size required by each data type in the table that follows. Remember that UltraLite only uses the maximum hash size if it really needs to, and it never exceeds the maximum hash size you specify. UltraLite always use a smaller hash size if the column type does not use the full byte limit.

Data typeBytes used to hash the entire value
FLOAT, DOUBLE, and REALNot hashed.
BIT and TINYINT1
SMALL INT and SHORT2
INTEGER, LONG and DATE4
DATETIME, TIME, TIMESTAMP, and BIG8
CHAR and VARCHAR

To hash the entire string, the maximum hash size in bytes must match the declared size of the column. In a UTF-8 encoded database, always multiply the declared size by a factor of 2, but only to the allowed maximum of 32 bytes.

For example, if you declare a column VARCHAR(10) in a non-UTF-8 encoded database, the required size is 10 bytes. However, if you declare the same column in a UTF-8 encoded database, the size used to hash the entire string is 20 bytes.

BINARY

The maximum hash size in bytes must match the declared size of the column.

For example, if you declare a column BINARY(30), the required size is 30 bytes.

UUID16

For example, if you set a maximum hash size of 6 bytes for a two-column composite index that you declared as INTEGER and BINARY (20) respectively, then based on the data type size requirements, the following occurs:

The row data

The row values of the data being stored in the database also influence the effectiveness of a hashed index.

For example, if you have a common prefix shared among entries of a given column, you may render the hash ineffective if you choose a size that only hashes prefixes. In this case, you need to choose a size that ensures more than just the common prefix is hashed. If the common prefix is long, you should consider not hashing the values at all.

In cases where a non-unique index stores many duplicate values, and UltraLite cannot hash the entire value, the hash likely cannot improve performance.

The database size

Each index page has some fixed overhead, but the majority of the page space is used by the actual index entries. A larger hash size means each index entry is bigger, which means that fewer entries can fit on a page. For large tables, indexes with large hashes use more pages than indexes with small or no hashes. This makes the database file bigger, and may (in some cases) decrease performance. This happens because page swapping can occur in the database cache as the cache can only hold a fixed number of pages.

The following table gives you an approximation of how the hash size can affect the number of pages required to store data in an index:

TablePage sizeHash sizeNumber of entriesPages required
Table A4 KB012003 pages
Table B4 KB32 bytes116 3 pages
Table C4 KB32 bytes1200 entries11 pages
See also

Setting the maximum hash size