Sparx Systems Forum
Enterprise Architect => General Board => Topic started by: Ian Mitchell on March 28, 2019, 08:28:58 pm
-
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 :)
-
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 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
-
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
-
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.
-
@Querty - See - you just couldn't resist the temptation. ;)
I think Geert's explanation seems the most believable.
@Sparx - care to comment?
-
Note regarding IDs they can be changed when invoking the reset IDs on the repository. GUIDs are pretty much set in stone
-
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
-
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
-
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.
-
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
-
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
-
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
-
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://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://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/ (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
-
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?
-
In this particular case we were talking about it in the context of the EA database.
The biggest ones I've seen are about 500 MB when transferred to .eap
Do you think we would notice the use of GUID's instead of the int id's in terms of performance?
And since the GUID fields are already there, and are already indexed as well, getting rid of the integer ID's in favor of the GUID's as primary key would even decrease the size of the database not?
Geert
PS. I've worked as an architect on a large administrative project ( ~3 M lines of C#) that used GUID's for all of it's primary keys, except for reference tables. It worked remarkably well with an SQL Server database of a couple of terrabytes.
The main advantage was that the business code could generate the primary key of an object in it's constructor, before saving it to the database.
-
Depending on what do you mean by 'noticing any difference'. probably not much in a scale how overall EA slowness (it can call 1000 db queries for single operation). If someone accepts to wait 4 secs, 4.5sec would accept too for sure. But is there any true reason of even trying this?
Moreover
1. you have to consider cross-database compatibility for EA project storage, ie not all databases supports GUIDs. Of course using varchar would work... the same way as using varchar for decimals ;)
2. In general, generating PK in business code is wrong pattern. At least because ID belongs to data layer, not to business one. Also: databases has built in mechanisms avoiding creating duplicated PKs. Those mechanisms are proven already. Even if 2 records are being created in exactly same time, distinct IDs will be assigned to them. Building new software, you cannot be 100% sure about it.
In practice, such easy initially change, proposed just to make programming kinda easier (for programmers not familiar with rdbms) might bring a lot of consequences, you don't even imagine about at project start. Maybe you will never be required to cover them. Lucky you. But there is quite big risk that one day it rise up in a most unexpected moment. Because fixing it might be not an easy task (le'ts say due to amount of data already collected) the cost might be huge.
And this all just because some one has a feeling that GUID fits his workflow better... You know what? I met programmers who rejects using of FK constraints because their limits them; or relational databases at all in favor of nosql storage just because it's easier to serialize objects directly into storage engine.
It seems to me like attempt to invent the wheel again, just because square wheel would fit better a trunk ;)
Using non integer PKs, is not wrong if it's justified. For example it's nothing wrong to use ISO code of currency or country as PK. But in general, it's anti-pattern, as long as we are talking about default use-cases of RDBMs. However the decision fits better some academic levels. Without deep knowledge of the systems (specially RDBMS, how those are built, and a theory behind) we can rise only practical arguments, when those are not enough to change the principles. I believe it would be possible to prove it using a formal syntax TBH I'm not capable to prove my point other way than giving some simple examples unfortunately. But having 20 years experience with RDBMS systems, I'm pretty confident in this area.
-
Depending on what do you mean by 'noticing any difference'. probably not much in a scale how overall EA slowness (it can call 1000 db queries for single operation). If someone accepts to wait 4 secs, 4.5sec would accept too for sure. But is there any true reason of even trying this?
Moreover
1. you have to consider cross-database compatibility for EA project storage, ie not all databases supports GUIDs. Of course using varchar would work... the same way as using varchar for decimals ;)
2. In general, generating PK in business code is wrong pattern. At least because ID belongs to data layer, not to business one. Also: databases has built in mechanisms avoiding creating duplicated PKs. Those mechanisms are proven already. Even if 2 records are being created in exactly same time, distinct IDs will be assigned to them. Building new software, you cannot be 100% sure about it.
In practice, such easy initially change, proposed just to make programming kinda easier (for programmers not familiar with rdbms) might bring a lot of consequences, you don't even imagine about at project start. Maybe you will never be required to cover them. Lucky you. But there is quite big risk that one day it rise up in a most unexpected moment. Because fixing it might be not an easy task (le'ts say due to amount of data already collected) the cost might be huge.
And this all just because some one has a feeling that GUID fits his workflow better... You know what? I met programmers who rejects using of FK constraints because their limits them; or relational databases at all in favor of nosql storage just because it's easier to serialize objects directly into storage engine.
It seems to me like attempt to invent the wheel again, just because square wheel would fit better a trunk ;)
Using non integer PKs, is not wrong if it's justified. For example it's nothing wrong to use ISO code of currency or country as PK. But in general, it's anti-pattern, as long as we are talking about default use-cases of RDBMs. However this discussion fits better some academic levels. Without deep knowledge of the systems (specially RDBMS, how those are built, and a theory behind) we can rise only practical arguments, when those are not enough to change the principles. I believe it would be possible to prove it using a formal syntax TBH I'm not capable to prove my point other way than giving some simple examples unfortunately. But having 20 years experience with RDBMS systems, I'm pretty confident in this area.