Jump to content
VictorLion

Performance of using UUID as a primary key

Recommended Posts

I am building a customer database. The database will have a primary key. I am looking to make the primary key either a unique integer or a UUID (with 32 chars). The UUID appeals to me due to its ability to sync tables. The primary key will be used for (obviously):

  1. Searching and sorting records
  2. As a tag against documents eg document XX is linked to customer AA, BB and CC; and 
  3. A foreign key to link tables

My questions are:

  1. Do UUIDs adversely affect database size
  2. Do UUIDs adversely affect speed

If UUIDs retain their full 32 chars, then there will obviously be a disadvantage.

However, is FM clever enough to convert the UUID "under the hood" (such as to an integer referenced index) to improve database speed and/or size.

Share this post


Link to post
Share on other sites
5 hours ago, VictorLion said:

 

  1. Do UUIDs adversely affect database size

 

Yes.  But does it matter?  How many records do you expect?

 

5 hours ago, VictorLion said:

 

  1. Do UUIDs adversely affect speed

 

No.

 

Share this post


Link to post
Share on other sites

Indexing a primary key stored as text may add some overhead, but not generally too large of a concern unless your tables are expected to get very large. The built in function for UUID will need to be stored as text for relationships to work. If you still want to use integers for UUIDs there are other algorithms to produce those. I would consider Jeremy Bante's custom function which you can find here:

https://github.com/filemakerstandards/fmpstandards/blob/master/Functions/UUID/UUIDTimeDevice.fmfn

Share this post


Link to post
Share on other sites

Using text UUIDs can make certain database operations very slightly slower than their numeric equivalents, but I've never seen an application where that was the performance bottleneck. There's practically always something else you can do to get a more substantial speed improvement.

If you do insist on numeric UUIDs, my function that Mike linked to could work. I started that family of functions before FileMaker introduced the Get ( UUID ) functions. Now that we have Get ( UUID ), you can just use one of many functions to convert Get ( UUID ) from hexadecimal to decimal. This is slower to generate each UUID, but the resulting UUIDs are slightly smaller and there's better security since the content is driven by the random number generator behind Get ( UUID ). (The result of the Get ( UUID ) function is a type 4 UUID, which is supposed to be created by a cryptographic-strength random number generator.)

  • Like 1

Share this post


Link to post
Share on other sites
9 hours ago, VictorLion said:

The primary key will be used for (obviously):

  1. Searching and sorting records

UUIDs are random values; using them for sorting is meaningless.

 

9 hours ago, VictorLion said:

Do UUIDs adversely affect database size

To some extent. You can minimize this extent significantly (IMHO) by allowing only minimal indexing of the UUID field.

 

9 hours ago, VictorLion said:

Do UUIDs adversely affect speed

Not as such, but ... If you also  elect to validate the field as unique, that might slow down some operations, e.g. creating new records, as the number of records increases. There's a dilemma here: the probability of generating a duplicate is extremely small - but it's not entirely zero.

 

9 hours ago, VictorLion said:

However, is FM clever enough to convert the UUID "under the hood" (such as to an integer referenced index) to improve database speed and/or size.

I don't know that there would be a perceptible improvement in either speed or size. "Under the hood" everything is kept as binary numbers.

 

Edited by comment
  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Who Viewed the Topic

    9 members have viewed this topic:
    moriya0238  MikeKD  dkey  doughemi  Lee Smith  BruceR  LaRetta  comment  Steve Martino 
  • Similar Content

    • By Hampden Tech
      I am seeing an error after syncing when I have a new record on the spoke (FileMaker DB) to be synced into a Microsoft SQL server table on the hub. The MirrorSync setup uses a custom primary key to do the insert into the SQL table.
      According to the sync window as well as the sync log, the insert fails. However, when I review the table in SQL Server on the hub, the record has indeed been inserted properly. For some reason, MirrorSync is reporting this as an error. In addition, MirrorSync does not seem to remember the PK of the newly inserted hub record so if I do another sync on the spoke, it inserts it again. If I run the sync 3 times, I will have 3 new records inserted.
      This is the error that I see in the log. No further information is available.
      table Hub node MS SQL Server/KeyPhrases failed for source nodeId '275' This also happens on another table as well.
      Is this a bug in version 4 of MirrorSync or is there some configuration step that is missing?
      Any help would be greatly appreciated, as this is preventing us from releasing this solution to production.
      Thanks!
    • By Hampden Tech
      I have a situation that is causing me some issues. In my MirrorSync configuration I am able to download data from the HUD, make changes in the spoke DB and then sync back up and the changes are properly reflected in the HUB (Microsoft SQL) database.
      However, I tried to add a new record on the spoke DB and when I did a sync operation, the error on the HUB indicated that the primary key field could not be null.
      We manage the primary keys in our SQL database ourselves, so the next number for each table is stored and updated within the DB. MirrorSync must be expecting that our HUB table is using an Identify column to set the primary key. There is no way that I see of specifying the primary key value in the HUB database. In other words, if I could match up a value in the spoke DB with the primary key and then insert it, I could use an insert trigger on the HUB database to catch this and then assign it a new number in sequence. For example, I could setup a new field in the table called "Temp PK" and set it to some magic number like "999916". Then, I can trap for this on an insert trigger in SQL and assign it the correct way. The only issue that I see with this is that
      1. I'm not sure how MirrorSync will be able to match up this new HUB PK with the Spoke PK, and
      2. I'm not sure if there would be any conflict issues with multiple users syncing at the same time
       
      Any advice on how to work around this would be helpful.
    • By lan
      Environment:
      FMS 15 Windows Server 2012 host on Azure Plug in: 360Works ScriptMaster Purpose for using the Plug in: zip the pdf files. Problems We have:
      There are a large amount temp files created on the client machine which run the fmp to connect the server , the temp files won't removed. What I am looking for:
      Any script step can avoid these temp files stay in temp folder. How these files created? I attached a screenshot.
       
      Thanks

       
    • By TJ53
      Let’s say we have two related tables: “Invoice” and “Invoice_Item”. We could create a calculation field in the “Invoice” table called “total_amount” with this formula:
      total_amount = Sum (Invoice_Item::amount)
      This field would have a negative impact in performance when appearing in the layout, since it would have to be defined as unstored, because it’s referencing a field from a related table.
      Now let’s suppose this field is not used for any scripts, tooltips, conditional format, etc … would the performance of the database be negatively affected ONLY when this field appeared in a layout? 
      In other words, would adding an unstored calculation field to a table involve a performance penalty, even in the “unreal” case where this field didn’t appear in any layout, script, conditional format, etc.? thanks in advance!
    • By TJ53
      Is there any difference in terms of performance between a calculation field (stored and indexed) and field defined as auto-enter calculated value (indexed)?
      For example, we have an “INVOICE” table, with a field called “date_invoice_sent”, and we’d like to have a boolean field called “is_sent”.
      The calculation would be “not IsEmpty(date_invoice_sent)”
      So we have two options here:
      - Calculation field (stored, number result).
      - Number field defined as “auto-enter / calculated value / do not replace … unchecked”.
      Would there be any difference in performance between the two options? thanks in advance!
×

Important Information

By using this site, you agree to our Terms of Use.