Wow, I knew I came to the right place, thanks!

"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."

This is exactly the approach I went with due to maximum portability. I
already wrote a class in C# that handles the conversion in a very efficient
way (binary to string, string to binary, I'm happy w/ the benchmarks.

First and foremost the database needs to remain fast, my application is much
more flexible for performance/scalability tuning so I'll worry about that
next. Char(16) is probably the best option at this point.

Thanks again!

_____

From: Alexander Gräf [mailto:graef@...]
Sent: Saturday, December 11, 2004 10:58 AM
To: firebird-support@yahoogroups.com
Subject: AW: [firebird-support] binary or string?


-----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


Yahoo! Groups Sponsor

ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=129kb7ess/M=294855.5468653.6549235.3001176/D=gr
oups/S=1705115386:HM/EXP=1102874330/A=2455397/R=0/SIG=119u9qmi7/*http://smal
lbusiness.yahoo.com/domains/> click here

<http://us.adserver.yahoo.com/l?M=294855.5468653.6549235.3001176/D=groups/S=
:HM/A=2455397/rand=152061676>

_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




[Non-text portions of this message have been removed]