9 Replies Latest reply on Apr 1, 2015 6:06 PM by user19752

    UUID as decimal for faster performance

    taylorsharpe

      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. 

        • 1. Re: UUID as decimal for faster performance
          jbante

          Taylor Sharpe wrote:

           

          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.

           

          Type 1 UUIDs include this information (usually with much greater resolution than just a per-second timestamp). Type 1 UUIDs in canonical RFC 4122 representation still don't help with sorting, which is why my type 1 UUID functions are structured a little differently (but contain the same information as a standard type 1 UUID, bit for bit).

          • 2. Re: UUID as decimal for faster performance
            taylorsharpe

            FYI, here is the calc I normally use.  I'm sure there is someone who has a shorter or better way to do it, but this works for me.  If you have a better formula for hex conversion, please share it. 

             

             

            Let ( [

             

            F1 = Get ( UUID ) ;

            F2 = Substitute ( F1 ;

              [ "0" ; ".0." ] ;

              [ "1" ; ".1." ] ;

              [ "2" ; ".2." ] ;

              [ "3" ; ".3." ] ;

              [ "4" ; ".4." ] ;

              [ "5" ; ".5." ] ;

              [ "6" ; ".6." ] ;

              [ "7" ; ".7." ] ;

              [ "8" ; ".8." ] ;

              [ "9" ; ".9." ] ;

              [ "A" ; ".A." ] ;

              [ "B" ; ".B." ] ;

              [ "C" ; ".C." ] ;

              [ "D" ; ".D." ] ;

              [ "E" ; ".E." ] ;

              [ "F" ; ".F." ] ;

              [ "-" ; "" ] ) ;

            F3 = Substitute ( F2 ;

              [ ".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" ] ) ;

            F4 = Get ( CurrentHostTimeStamp ) ;

            F5 = GetAsNumber ( F4 ) ;

            F6 = F5 & F3

             

            ] ;

             

            F6

             

            )

            • 3. Re: UUID as decimal for faster performance
              siplus

              In my case I use get(UUID) as key in some tables only because I like constant-length keys and at the same time I'm lazy.

               

              But IMHO it would be enough to use a numeric key auto-enter, starting with 100000000000.

               

              The problem arises when I import client data into updated solution.

               

              If I click "perform auto-enter options while importing (modification date, serial number, lookups, etc)

               

              • [ brain explode moment: can a serious database system use the ending "etc." in this crucial moment, for 20 years I've swallowed a concrete or virtual Prozac before, while and after checking or unchecking this option]

               

              and my sn is autoenter number, I am sure that the new records will get a new and unused sn, while when my sn is based on get(uuid) I don't have that guarantee - an imported calculated indexed sn field won't be recalculated and it might collide with an existing one. But otos I might want to keep my old sn's because I have rels on them, so I unclick the aforementioned option. Problem is, I can't choose to selectively perform or not the auto-entering.

              • 4. Re: UUID as decimal for faster performance
                taylorsharpe

                The other issue that happens is if you have syncing solutions where someone offsite and not connected to the database is creating records, their serial number may be the same number as records you are creating live on the database.  Then when you sync them, then no bueno.  So UUID solves that problem.  Also, there are issues about assigning a serial number on create or commit and if you have a lot of users doing this simultaneously, it makes a difference.  Using UUID solves any of those issues too. 

                • 5. Re: UUID as decimal for faster performance
                  siplus

                  I had good experience with using this as sn:

                   

                  Get ( CurrentTimeUTCMilliseconds ) & Right("0000000000000" & Int(Random * 1000000000); 10)

                   

                  but if you want to play on the safe side you can add

                   

                  10^50 - (Filter(Get(SystemNICaddress);"01234567890"))

                   

                  to the mix.

                  • 6. Re: UUID as decimal for faster performance
                    taylorsharpe

                    Siplus, I used to use the same thing with just CurrentTime (before you could get milliseconds) and it worked just fine. 

                     

                    One thing that is happening if you are at a site that has to do a lot of documentation (e.g., federal government security plans, ISO 27000 documentation, etc.), following industry standard goes a long ways and UUID has come to be a standard to replace such things as random number generators.  There used to be some challenges on how random the random number generators were and I know FileMaker's random number generator was one of the ones accused of not being truly random. 

                     

                    For practical purposes, your solution works just fine.  I use the UUID's to stick with industry standards and saying it is still a UUID, just in decimal form, will sound good for documentation purposes. 

                    • 7. Re: UUID as decimal for faster performance
                      jbante

                      Get ( SystemNICAddress ) is no longer a safe function for this purpose in FileMaker. FileMaker Go will return the same value for all current devices. Get ( PersistentID ) is the appropriate substitute.

                      • 9. Re: UUID as decimal for faster performance
                        user19752

                        You have simple version youself,

                        Re: UUID hex verses Decimal Serial Number for Primary Key

                        are there any problem for Substituting two times?

                         

                        I think

                        Substitute ( text ; [ search1 ; replace1 ] ; [ search2 ; replace2 ] )

                        should work exactly same as

                        Substitute ( Substitute ( text ; search1 ; replace1) ; search2 ; replace2 )

                        AKA left to right order, but not described clearly as Case() function does.

                         

                        I tried simplify but not so succeeded. (NOT tested enough)

                         

                        Let ( [

                        $U = Substitute ( Get ( UUID ) ; "-" ; "" ) ;

                        $i = 0 ;

                        S = Substitute ( 10 ^ 32 - 1 ; 9 ; "& SerialIncrement ( \"00\" ; Position ( \"123456789ABCDEF\" ; Middle ( $U ; Let ( $i = $i + 1 ; $i ) ; 1 ) ; 1 ; 1 ) )" )

                        ] ;

                        Evaluate ( "GetAsNumber(Get(CurrentHostTimeStamp))" & S )

                        )

                         

                        Another one, convert to real hex value to dec

                         

                        Let ( [

                        $U = Substitute ( Get ( UUID ) ; "-" ; "" ) ;

                        $i = 0 ;

                        S = Substitute ( 10 ^ 32 - 1 ; 9 ; "+16^(32 - $i) * Position ( \"123456789ABCDEF\" ; Middle ( $U ; Let ( $i = $i + 1 ; $i ) ; 1 ) ; 1 ; 1 )" )

                        ] ;

                        GetAsNumber(Get(CurrentHostTimeStamp)) & "." & Evaluate ( S )

                        )

                         

                        I put UUID in fractional part in this for sorting (decimal has not fixed length), I don't know this is good or not. If bad, you can insert 0.