IDs and GUIDs

[Geert Bellekens]

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.

[MaXyM]

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.

[MaXyM]

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.