| Subject | AW: [firebird-support] binary or string? |
|---|---|
| Author | Alexander Gräf |
| Post date | 2004-12-11T17:58:23Z |
-----Ursprüngliche Nachricht-----
Von: Storage Box [mailto:spam@...]
Gesendet: Samstag, 11. Dezember 2004 10:39
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] binary or string?
Some weeks ago, I tried to store UUIDs (128 bit long). I tested several things, and UUIDs can be stored in different ways:
First, you can store the string represention, which is like this:
C16BE618-1C01-461a-9D5D-38B64DC1C1AB
This are 36 characters, and when used as PKEY/FKEY in table joins, the thing gets real slow. However, it makes the database very portable.
Second, you can split the represention into two 64bit integers. I never tested that, but I think it would be a lot faster than the first approach.
Third you can store the binary representation in the database, as
CHAR(16) CHARACTER SET OCTETS
This is the fastest approach internally, but makes handling with .NET a bit jerky, because you need to do several conversion steps for getting the stringified binary data from the Uuid.
To answer your question: Simply put the value into a 64bit int, and get your string representation via UDF. Anything else will prove to be a design error when the database grows, because you're wasting 10 bytes for every PKEY/FKEY in your database, and in every join, a comparation between two keys takes at least double the time (maybe it is more, because 64bit can be compared natively, where CHAR/VARCHAR needs to be compared via memcmp).
Regards, Alex
Von: Storage Box [mailto:spam@...]
Gesendet: Samstag, 11. Dezember 2004 10:39
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] binary or string?
Some weeks ago, I tried to store UUIDs (128 bit long). I tested several things, and UUIDs can be stored in different ways:
First, you can store the string represention, which is like this:
C16BE618-1C01-461a-9D5D-38B64DC1C1AB
This are 36 characters, and when used as PKEY/FKEY in table joins, the thing gets real slow. However, it makes the database very portable.
Second, you can split the represention into two 64bit integers. I never tested that, but I think it would be a lot faster than the first approach.
Third you can store the binary representation in the database, as
CHAR(16) CHARACTER SET OCTETS
This is the fastest approach internally, but makes handling with .NET a bit jerky, because you need to do several conversion steps for getting the stringified binary data from the Uuid.
To answer your question: Simply put the value into a 64bit int, and get your string representation via UDF. Anything else will prove to be a design error when the database grows, because you're wasting 10 bytes for every PKEY/FKEY in your database, and in every join, a comparation between two keys takes at least double the time (maybe it is more, because 64bit can be compared natively, where CHAR/VARCHAR needs to be compared via memcmp).
Regards, Alex