Some time back, Winfried Huslik did some comparisons. The difference in indexing is significant, both in terms of file size and in terms of speed. (You might be able to find his results if you search the forum.)
Jeremy Bante has posted a numeric UUID CF (FileMaker Custom Function:UUIDRandom) that appears to fit the bill. But you probably know that already.
About the only downside is the need to jump through some hoops to implement a multi-key if you go the numeric route. But you probably know that already, too.
Thanks, Mike. I had not seen the custom function you referenced, by had seen one by Todd Geist that appears to do the same thing.
I'm afraid I'm not sure what you mean by implementing a multi-key. Is this like combining a time stamp and the random function?
The custom function seems to work well, but just did this calculation to convert the hex to decimal and am going to try it for a primary field on a project I'm working on now. Thanks for the info.
Let ( [
F1 = Get ( UUID ) ;
F2 = Substitute ( F1 ;
[ "0" ; "00" ] ;
[ "1" ; "01" ] ;
[ "2" ; "02" ] ;
[ "3" ; "03" ] ;
[ "4" ; "04" ] ;
[ "5" ; "05" ] ;
[ "6" ; "06" ] ;
[ "7" ; "07" ] ;
[ "8" ; "08" ] ;
[ "9" ; "09" ] ;
[ "A" ; "10" ] ;
[ "B" ; "11" ] ;
[ "C" ; "12" ] ;
[ "D" ; "13" ] ;
[ "E" ; "14" ] ;
[ "F" ; "15" ] ;
[ "-" ; "" ] ) ;
F3 = GetAsNumber ( F2 )
Multi-key refers to using a return-delimited list as a key field. It's a way of mimicking a many-to-many join without using a join table. Usually more useful for temporary scripting operations than for actual relational modeling.
The trick is, you can't do it with a number field. (The paragraph characters get stripped out.) So you have to use a separate text field and then shove the list into that field. Plus have the extra TOs on the Graph. Bit more work than just using something that already is formatted as text.
Minor inconvenience, in most circumstances. But something to be aware of.
In case you haven't read it, there is a discussion of some different types of UUIDs on the filemakerstandards.org website.
Jeremy Bante posted the results of some speed testing that you may find useful.
This function makes 64 digit number, so it may be a bit wasting file size than converting hex to dec (39 digit).
Here is jbante's thread Comparing Get ( UUID ) with existing solutions
There may be a little mistake in the thread Re: Comparing Get ( UUID ) with existing solutions
that + Floor ( Random * 2^62 ) is not a real random number since Random has only 17 digit on FMP on Windows.
On Mac it get 20 digit and enough for 2^62.
I personally use FileMaker's Get ( UUID ) function and convert it to a Base10 number. It works well, and I've seen very little performance impact.
As others already mentioned and the linked discussions proved, the performance difference is significant - as expected. Even thought numbers are internally stored as text in FileMaker, their indexing and sorting is a lot more efficient, so it's naturally faster.
More important question, however, is whether the difference has significant impact on your solution's performance and that's completely different story.
I always suggest making decisions based on the actual conditions.
I have recently worked on a solution where using Get(UUID) was fast enough and the fact that it's immediately available, easy to remember and fast to type saved me more time in development than it will ever waste by being a little bit slower.
In another solution, where millions of records are generated, I used the good old incremental numeric index because it's the fastest solution, and since there is no synchronization with other data sources involved, I did not have to care about uniqueness of the IDs ouside of that table.
In yet another solution we created several years ago uniqueness was so critical that even Get(UUID) as it is currently implemented could not be used even if it existed at that time.
Probably the best thing you can do is to save the links to the numeric UUID custom functions for cases when you need this kind of functionality and indexing efficiency is important, use plain serial numbers where speed is most critical, and make your life easy everywhere else ;-)