On Dec 24, 2011, at 1:18 AM, Mark Rotteveel <mark@...> wrote:

>>
>
>
> Are you really using concatenated strings containing several distinct
> dataitems as your key? Given your example the identifier for Manhattan
> is 27 bytes, if you would use a key of seperate fields you need a
> CHAR(2), and 3 INTEGERs so 2 bytes + 3*4 bytes = 14 bytes. Assuming that
> the identifier for NEW YORK(State) is globally unique, you probably
> don't even need to use the US prefix losing 2 bytes.
>
> This would almost halve the size of the required indexes and I would
> expect that to be faster for lookup than such a concatenated string.

Maybe making the key smaller would help, but remember that Firebird does prefix compression and a degree of suffix compression on keys. All instances of 'US' after the first one in an index segment disappear, as do the numbers that represent New York City. 80% of the time, the index contains only those characters necessary to make the key different from the one before it, plus whatever comes after unless it's spaces (for character types) or zeros (for numerics).

>
> If you insist (or really need) to use such a concatenated identifier,
> you might be better off looking at a NoSQL solution (although I admit I
> don't know enough about NoSQL to substantiate that claim).

I don't know a lot about NoSQL solutions, but this application does a lot of qualification of records on secondary keys which are generally not a NoSQL feature.


Good luck,

Ann