0

Currently I am involved in learning some basics of the Java EE technology. I encountered a particular project and took a deeper look into the underlying database structure. On server-side I investigated a Java function that creates a primary key with a length of 32 characters (based on concatenating the time, a random hash, and an additional cryptographic nonce).

I am interested in a estimation about the performance loss caused by using such a primary key. If there is no security reason to create such kind of unique IDs wouldn't it be much better to let the underlying database create new increasing primaries, starting at 0?

Wouldn't a SQL/JQL search be much faster when using numbers instead of strings?

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
John Rumpel
  • 4,535
  • 5
  • 34
  • 48

1 Answers1

1

Using numbers will probably be faster, but you should measure it with a test case if you need the performance ratio between both options.

I don't think number comparison vs string comparison will give a big performance advantage by itself. However:

  • larger fields typically means less data per table block, so you have to read more blocks from DB in case of a full scan (it will be slower)
  • accordingly, larger keys typically means less keys per index block, so you have to read more index blocks in case of index scans (it will be slower)
  • larger fields are, well, larger, so by definition they are less space-efficient.

Note that we are talking about data size and not data type: most likely a 8-byte integer will not be significantly more efficient than a 8-byte string.

Note also that using random IDs is usually more "clusterable" than sequence numbers, as sequences / autonumerics need to be administered centrally (although this can be mitigated using techniques such as the Hi-Lo algorithm. Most curent persistence frameworks support this technique).

Community
  • 1
  • 1
gpeche
  • 21,974
  • 5
  • 38
  • 51