Comparing Get ( UUID ) with existing solutions

Discussion created by jbante on Apr 18, 2012
Latest reply on Aug 30, 2017 by 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 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.