UUID as decimal for faster performance

Discussion created by taylorsharpe on Mar 31, 2015
Latest reply on Apr 1, 2015 by user19752

The Get ( UUID ) function returns hexadecimal values.  Many of us now use these as primary keys.  FileMaker has a limited number of field types and for numbers, it is only numbers (unlike most big SQL databases that have 9 or so number formats such as hexadecimal, integer, binary, etc.).  For FileMaker, hexadecimal numbers have to be made into Text fields.  Apparently FileMaker can handle sorting and matching and such functions faster with number fields than text fields.  Using a UUID as a hexadecimal stored in a Text field is not as fast as a serial number field.  The solution has been to write a formula that gets the UUID and converts it to decimal values and save it in a number field, then you get faster functioning as a key field.  John Sindelar and others who like custom functions just write one of those for reuse and if you look in his Seedcode products, you'll see the UUIDdec custom function


I prefer to avoid custom functions... just a person preference.  So I write a simple Let calculation when defining the primary key as a number field that gets the UUID and converts its character into a decimal.


One thing I don't like about UUIDs compared to serial numbers is that you loose sequencing of the order of creation.  So what I do in the primary key calculation is after creating the  key as a decimal, I get the creation timestamp as a number and concatenate it to the front of the decimal UUID and I save that as the primary key. That way all of my primary keys have a number in the creation sequence order. This gives me very unique numbers, fast FileMaker processing, and preserves the value of number sequencing you get from a serial number. 


Keep in mind that performance differences are probably not noticeable for a few thousand records.  So converting to decimal UUID is mostly beneficial if you have hundreds of thousands or millions of records.