Comparing Get ( UUID ) Performance

[jbante]

I've been using my own UUID function for a while, so I was eager to see how it and other UUID solutions the FileMaker community has developed compare to the new Get ( UUID ) function. I put a test file together, and I encourage anyone interested to download it and run the tests for themselves in case someone runs into something I didn't, or in case someone just doesn't believe me. (I chose not to use any sub-second timing, like FMBench, for portability reasons, though I'm sure the results would be interesting. Here's hoping FileMaker 13 supports "SetPrecision ( Get ( CurrentTimestamp ) ; 6 )".) Here's what I found:

1. Get ( UUID ) calculates very fast — its performance is similar to the empty custom function I used as a baseline to measure the overhead of running the tests.

Good calculation speed is worth pursuing, but if you're using the value as a primary key in record data, it's probably the less important. The value will only be calculated once for the life of each record, but may be referenced for finds and relationship matches several times and will contribute to file size over the entire life of the record. With that in mind...

2. IDs that can be stored in number fields are consistently much faster to perform finds on than text fields. In my testing, at least twice as fast, and usually an order of magnitude faster.

3. My test of the time to Count () related records matched on UUID showed no meaningful performance difference between the formats I tested. ExecuteSQL () similarly showed no meaningful difference.

4. The IDs that can be stored in number fields result in file sizes slightly smaller than Ray Cologon's Base 36 solution (despite the number values being longer), which is significantly smaller than a file using Get ( UUID ).

I'm pleased that FileMaker introduced a Get ( UUID ) that developers can standardize on; but without a change in the performance characteristics of text fields (required for storing the value) compared to number fields, I'm considering sticking with numeric UUIDs.

A Get ( UUID ) value can be converted from base 16 to base 10 for storage in a number field. This completely elimates the calculation speed advantage of Get ( UUID ), but I did say above that this may not be the top priority.

Find performance and file size for this converted value are modestly better than for my own numeric UUIDs

. (The value is a couple digits shorter, which I'm presuming makes indexes a little smaller.) This doesn't contain any information value from some of the other functions, but that's better for certain applications anyway.

UUIDTest.zip

[Tom R.]

I was just wondering about the Get ( UUID ) function this morning. I know the UUID function over at filemakerstandards can be parsed to get various pieces of data such as (I believe) the value's creation timestamp. Does anyone know whether UUIDs created via FileMaker's function can be parsed also?

[jbante]

The value returned by FileMaker's Get ( UUID ) function is randomly generated. The only data that can be parsed from it is the fact that it's (designed to be) random, indicated by a flag byte in the value. You can tell this because Middle ( Get ( UUID ) ; 15 ; 1 ) will always give you 4, which is the RFC 4122 standard version number for randomly generated values.

[genevieve charbon]

A Get ( UUID ) value can be converted from base 16 to base 10 for storage in a number field. This completely elimates the calculation speed advantage of Get ( UUID ), but I did say above that this may not be the top priority.

So why FMI didn't create such numeric ones ? to make FMP slower once more ?

How do you make such a conversion ?

Thanks for this very interesting Post !

[Russell Barlow]

How do you make such a conversion ?

Here is informational link from over at Filemaker Standards. From the day Jeremy showed the performance increases with using numeric UUID, I've been using religiously. Works great.

[jbante]

So why FMI didn't create such numeric ones ? to make FMP slower once more ?

How do you make such a conversion ?

The hexadecimal result of Get ( UUID ) is the canonical representation for an RFC 4122 value. The specification is actually for a binary format, and tradition says that hex is an acceptable way to display binary data for nerd-human consumption. Having Get ( UUID ) return a decimal result would surely inspire criticisms of failure to comply with standards and conventions adopted by other technologies. FileMaker wants to be consistent with other technologies as much as is consistent with their vision of the product.

Since the result from Get ( UUID ) is essentially hexadecimal, you can convert it to a decimal number by Substitute()ing or Filter()ing out the hyphens and applying any of the available custom functions for converting between base 16 and base 10. Store the result in a number field, and you'll get the find speed and file size benefits. However, the calculation speed of that leaves something to be desired — converting from hex to decimal is relatively slow. This will give you the same result, and is much faster to calculate:

Floor ( Random * 2^48 ) * 2^80

+ 302231454903657293676544 // 4 * 2^76, version number for random UUID

+ Floor ( Random * 2^12 ) * 2^64

+ 9223372036854775808 // 2 * 2^62, reserved bits, indicating UUID scheme

+ Floor ( Random * 2^62 )

If you need the hex representation again, you can convert it back from base 10 to base 16 (again, there are many custom functions that will do this), pad it on the left with as many zeros as necessary to make the result 32 characters long, and insert hyphens in the right positions. (The grouping is 8-4-4-4-12.)