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


Optimal hash size limit

The UltraLite default maximum hash size of 4 bytes suits 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. This change can 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).

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. Observe how various hash sizes affect the application and query performance, in addition to the changes in database size itself.

Index hashing improves inserts, updates, deletes, and searches when the columns being indexed have a good distribution of values, such as strings that do not have a common prefix, at the cost of bigger index structures. Hashed indexes locate rows first by using the hash, and then by using direct row comparison to differentiate rows with the same hash value. If the hash size is sufficiently big, the hash uniquely identifies a row without reading and comparing the row. However, if the hash size is too big and the page size small, the index may need too many database pages.

 The data type
 The row data
 The database size
 Set the hash size
 See also