we have a discussion if it is worth to shorten the data in a PK-column. The column is VARCHAR(15), the data contents is also 15 character but more than half of it are redundant '0'-characters, means, the column contains data like that '000000000123456' while we know we need only '123456'. There are a lot of accesses to this table because this is a very central table referenced a lot of other tables via foreign key relations. That means the columns in those other tables are also subject of this optimization. The UL-Database runs on a Windows CE device on Flash Memory or SD-Card and we have optimization goals like overall performance and battery run-time.
Thanks for any hints,
asked 19 May '14, 03:39
It will go faster without the zero prefixes, and it will go even faster with an integer type. For these two cases, the default index value hashing is appropriate.
With the leading zeros, disabling the index value hashing will help. You can also try increasing the size of the index value hash to contain the entire key value. The maximum is 32 bytes.
As usual for performance decisions, to quantify the benefits you will need to test your application with the changes. Happily, adjusting the index value hash size is simple enough and doesn't require any application changes.
See the create table and create index statement 'with max hash size' clause.
answered 20 May '14, 12:20