Subject Re: [IBO] (unknown)
Author Helen Borrie
At 01:50 AM 27/03/2004 +0000, you wrote:
>I posted this in the FB group... but a response made me think that
>maybe this forum is a better place as I am using the IBO TDataset
>objects with Delphi to access my FB 1.5 database...
>We are preparing to convert a MS SQLServer7 database and have been
>desiging all the tables with PrimaryKeys of Char(20) - generated by
>our own routines, a bit like our own GUID...
>Most of our tables are moderate size with a few tables around 500,000
>to 1 million records.
>Most of our queries appear to be fast... but now we are worried we
>should have used an BIGINT (Int64) instead of our Char(20) for our
>I believe Ann once stated that a BIGINT and a Char(8) would be
>equivelant in the time it takes to compare values... so then a Char
>(20) would be 2.5 times longer... if my math isn't too rusty.

You can't really calculate it that way, because the indexes are compressed
bitmaps: searches will "glide over" matching sequences. It's an issue
when keys are larger but a 20-byte key wouldn't cause a significant difference.

If you are going to use GUIDs (and plenty do: it's a good solution when
replication is involved) then use character set OCTETS so that you never
need to bump into transliteration problems with keys.

>What are most people using for PK's?

Many IBO users would use "generatable" integers or BigInts because IBO just
makes it so darned smooth to get new key values into the client before
posting anything (GeneratorLinks).

>and can you _please_ offer your suggestions because we really want to
>hear something that will either push us one way or make us feel like
>we are sitting OK...

Myself, I wouldn't use GUIDs unless I had a need for them because, in big
batch inserts, it costs more to call an external function to get a GUID
than it does to pull a value off a generator. On the other hand, if there
was replication in the picture, read-write activity in briefcase versions
of the database, etc., then it wouldn't make sense to rule them out.