Subject Re: [firebird-support] Big DB
Author Elmar Haneke
> 1) Any tips? Will FB have trouble with this DB or potentially
> larger?

The DB size should be no problem for firebird 1.5 (or even 1.0 with
64Bit I/O) - you should make shure not to install it on an filesystem
with an 2GB filesize-limit.

> But one of the common lookups is on a varchar 100.

If you are using an 8Bit Charset and if you do not specify an
"Collation" This field can be indexed without any problems.

> In our old system we took a 32 bit hash of this field and put it in
> a "index". When we wanted to lookup, we scaned for matching hashes.
> If more than one, then we comparedt eh actual text of those with
> matching hashes. Very fast....

If performance gets critical you might do some performance-tests to
compare both search-methods. Usually searching the varchar directly
should be sufficienty fast.

To use the "hash" in FB you should:

- Add an UDF to calc the Hash-Value
- add the hashvalue as an integer column to yout table
- create an index on this column
- Create an trigger to automatically generate Hashes

The query would do "WHERE Hash=CalcHash('x') AND data='x'" instead of
"WHERE data='x'".

> Now I assume FB does something simlar for #2?

It's not really similar, it uses an BRtee-Index for the whole key, not
just for an hash-number.