Lester Caine wrote:

>>
>>My gut reaction is that Firebird would be faster if these three fields
>>were just INTEGER ( in fact a single byte would do since the three
>>tables are only a couple of dozen types though language could get bigger
>>)

Faster, but not all that much faster, given index prefix compression.
If the guid's are stored in a byte order that puts the least volatile
bytes first, the compression will eliminate them. Thus, in an imaginary
GUID, you might have fields for galaxy, solar system, planet, computer
manufacturer, computer, and sequence. For numbers generated on a single
computer, only the sequence changes. After the first entry on each
index page, all the fields except sequence disappear into prefix
compression, and only the changing bytes of the sequence change.
Maybe you could measure a difference in performance, but I doubt it.

>> This would reduce the record by 52 characters, but having to compare
>>strings rather than a single integer is surely a slower process.

Not an issue indexes. All indexes are compared bytewise, regardless of
type. That turns out to be important if you use compression.

>>
>>The other point of discussion is the addition of fields for security and
>>other management functions. These are currently being added as joined
>>tables, on the basis that not all records need to have a group_id or
>>source_id, but when looking at 100k items of content, all of which have
>>these to fields filled in, I can't see how managing a separate
>>content_id/group_id pair in a separate table gives you anything, and
>>even if only 10k of the group_id fields were populated, surely just
>>having NULL entries for the field in the main table is not going to be
>>an overhead?

Null entries are zero or blank filled, depending on type, and run length
data compression eliminates them.

>>
>>Obviously indexing on something like group_id where there may only be 10
>> or 20 values for all 100k records, would be a problem, but is there
>>any way to get round that?

Wait for V2. It fixes the index duplicate performance problems.

>>
>>Finally - will FB2 give any help in terms of performance when dealing
>>with these sorts of question.

Yes in the case of index duplicates. Also yes in the case of
compressing guid fields. Firebird 2 has a much more sophisticated
compression algorithm and uses indexes within indexes to improve
performance on large page sizes.

But, if I can make a suggestion, don't get into a theoretical p*sing
match with the Postgres guys. Build a sample and test it.

Regards,


Ann