IDs and GUIDs

[Ian Mitchell]

After 10 years of making addins for EA, and crawling into the tiny, secret places of the EA database, I have one nagging question.

Is there some deep logic to the use of ObjectID (long numbers, not globally unique) vs. GUIDs (globally unique strings) ?

I realise there may some blindingly simple explanation for this, which I just haven't spotted, but purely out of curiosity, does anyone see a pattern?
@querty - see if you can answer by saying something nice about EA. You know you can do it

[qwerty]

Simple: GUIDs are strings and valid over any number of repositories (at least in 99.99...% of all cases). Object IDs are numbers and valid only in one repository. Now guess which one is used as primary key in most cases and which is faster to search in a DB?

q.

[Geert Bellekens]

I think it's legacy.

My guess is that in the beginning (when the animals still talked) EA used only IDs, and had no GUIDs yet.
Then they realized they needed some kind of mechanism other then the auto-increased ID columns to identify an element across different repositories; hence the GUID.
They probably never saw a (business) need to refactor the code to completely get rid of the ID fields, which had become obsolete after introducing the GUID's.

Newer features often use the GUID's instead of the ID to link stuff

We can only guess, only a Sparx employee might know the answer for sure.

Geert

[Geert Bellekens]

Simple: GUIDs are strings and valid over any number of repositories (at least in 99.99...% of all cases). Object IDs are numbers and valid only in one repository. Now guess which one is used as primary key in most cases and which is faster to search in a DB?

q.

I wouldn't be too sure about the performance argument of the Object ID's. I've seen a lot of systems that use GUID's as their PK that had no issue with performance because of that choice.

There might be a difference, but it might also not be significant.

Geert

[qwerty]

I wouldn't be too sure about the performance argument of the Object ID's.

Don't argue with me :-) This is a late 20th century design. And that time it was definitely a performance issue.

q.

[Ian Mitchell]

@Querty - See - you just couldn't resist the temptation.

I think Geert's explanation seems the most believable.

@Sparx - care to comment?

[Sunshine]

Note regarding IDs they can be changed when invoking the reset IDs on the repository. GUIDs are pretty much set in stone

[Paolo F Cantoni]

Note regarding IDs they can be changed when invoking the reset IDs on the repository. GUIDs are pretty much set in stone

In general, IDs are also monotonically increasing.  Thus, in the absence of proper time-based footprinting, one can use the ID as a proxy for determining which item came before another.  We use that a lot in our automated processing.

Paolo

[Ian Mitchell]

Oh - I never thought of that.
Very interesting idea.
I've sort-of been using the idea for ages, only when debugging (looking into the repository database, sort by ID) , but never worked-out that it could be used more explicitly to show timing.
I'll think about that some more - Thanks

[qwerty]

Doesn't help you in finding out Element-Connector-Element sequences but only Element-Element or Connector-Connector. You'll need audit for the first.

q.

[Geert Bellekens]

Doesn't help you in finding out Element-Connector-Element sequences but only Element-Element or Connector-Connector. You'll need audit for the first.

q.

Don't rely on it too heavily. Adding packages to version control (and doing check-in/check-out) will completely ruin that order.

Geert

[Paolo F Cantoni]

Doesn't help you in finding out Element-Connector-Element sequences but only Element-Element or Connector-Connector. You'll need audit for the first.

q.

Don't rely on it too heavily. Adding packages to version control (and doing check-in/check-out) will completely ruin that order.

Geert

Another reason we DON'T use Version Control, but snapshots.

Paolo

[MaXyM]

Simple: GUIDs are strings and valid over any number of repositories (at least in 99.99...% of all cases). Object IDs are numbers and valid only in one repository. Now guess which one is used as primary key in most cases and which is faster to search in a DB?

q.

I wouldn't be too sure about the performance argument of the Object ID's. I've seen a lot of systems that use GUID's as their PK that had no issue with performance because of that choice.

There might be a difference, but it might also not be significant.

Geert

In fact this is a fact
Working with basic datatypes is always faster no matter what. Int datatype takes less space, therefore more data can fit in cache memory. The same apply for indexes over those data. With addition than indexes over integers are more efficient than ones over strings. In result all related operations are less demanding and more efficient. Please note it appear meaningful on each comparison operations: ordering, searching, comparing, joining, checking uniqueness etc
It's true, that today, with a lot of cheap resources this is not a case as 20-30 years ago. But still, it has to be considered when designing database systems.

There is also another strong argument: native support for PK automatic numbering. It's important to say, that this is not just incrementing of value. This mechanism is designed to work specifically in transactional environment causing no locks (this fact is mostly unknown for most developers). Without it, generating PKs with 100% guarantee of no collision, would be very expensive.

Anyway, pattern of using integer PK/FK in RDBMS is considered to be most efficient. So there is no reason to break the rule. Also no need to attempt proving opposite.

with regards

[Geert Bellekens]

There is quite a lot of interesting discussions to be found on this subject.

https://dba.stackexchange.com/questions/264/guid-vs-int-which-is-better-as-a-primary-key
https://stackoverflow.com/questions/11033435/why-is-identity-preferred-over-guid-as-primary-key-for-data-warehousing
https://blog.codinghorror.com/primary-keys-ids-versus-guids/
and others.

So it seems that even the DBA experts don't have a black and white answer.

Geert

[MaXyM]

I assure you DBA experts are pretty sure.
Programmers who just make an use of databases aren't. But they don't even understand what RDBMS are about (hence it's why they have no problems with GUID based PKs). Moreover, programmers are prone to get rid of RDBMS replacing them with noSQL once, regardless a use-case, seeing no consequences of such choice. For them it's only a storage. Even better if hidden behind ORM. They are forgetting that to get promised profits from RDBMS, such system has to be maintained in specific way (ie do you think they care about normal forms or even know what it is?)

I can see such trends recent years. Unfortunately consequences come later, often in production, even after people who made a design choice are not around anymore.

Just an example and consecutive question:
If int based index takes 40GB, GUID based index will take I guess 160GB. 160GB index might not feed RAM, query planer might even decide to not use it in favor of sequential scan (which obviously will be slow like hell).

I admit it really doesn't matter in projects not requiring a performance. The problem appears when a programmer with experience collected with such a project has to design more performant system. Obviously trying to apply his experience, persuading others that it will work.

Is it worth even considering suboptimal solution? Why not to go using proven way. Can you imagine how many resources is needed to be spent arguing against such ideas, proving those are wrong? Or later to fix systems which was working on programmers table but fail under production load?