[opencms-dev] OCMS 6beta1: questions about database design

[Claus Priisholm]

Hi, I do not know the rationale behind the decision to go with uuid's 
in OC6, but there are some general things that can be said.

2) typically uuid needs to be globally unique, hence quite long to make 
it a reasonable assumption.

3) primary key columns should be indexed anyway, so the performance 
overhead is minimal. The memory foot print is of course larger (with 
OCs uuid vs. int it's 36 bytes vs. 4 bytes), and the length may also 
cause tables and indices to span more blocks on the disk and that in 
turn may cause some performance degradation. In reality most tables 
contains many more bytes in the other columns so the relative change of 
going from 4 to 36 bytes is not significant.

4) I've mostly used uuid's in conjunction with object/relation mapping 
frameworks and there it gives you an opportunity to encode information 
about the entity class as well as provide a unique id in relation to 
the database tables. I haven't been digging into the database model in 
OC6, so I am not sure if that is a reason here, but the very least 
using uuid's makes it possible generate the id's without having to draw 
a number from the database (by a cross-database solution like in OC5 or 
by relying on some database vendor specific sequence number algorithm). 
Thus saving a server roundtrip when creating new records, which in turn 
may outweigh the at least theoretical performance hit described in 3.

Adding an extra primary key means a larger footprint (even if it is 
just 4 bytes), it also requires an extra index for the database to keep 
up to date and typically extra code in the application as well, so 
there would have to be some particular and important places where the 
uuid approach would be significantly inferior to an int as primary key 
for this to be worth it.

The usage of char over varchar should be an advantage. But to what 
extend certainly depends on the database system. It is likely that a 
database would implement short varchars the same way chars (except that 
it may actually save a bit time by not trying to space-pad varchars as 
opposed to chars).


On 11/3-2005, at 19.26, Kai Schliemann wrote:

> Hi list,
>
> in our company came up a few questions about the database design 
> (mostly
> from our db specialist) for the new 6 beta1. I want to forward them to 
> you
> for discussion and exchange of experiences with it. Especially what
> performance is concerned.
>
> 1. Why are all IDs now UUIDs?
> 2. Why are primary keys now relatively long varchars and no integers?
> 3. Did anyone do any performance testing especially on this issue and 
> what
> were the results?
> 4. Can anyone tell the advantages and disadvantages of UUIDs?
>
> Possible suggestions for optimization from our side are:
>
> 1. Usage of an internal integer ID as primary key in parallel and as
> addition to the UUIDs
> 2. Usage of datatype fixed char instead of varchar
>
>
> Looking forward to a good an valuable discussion on this issue.
>
> Thanks in advance.
>
> Regards
>
> i.A. Kai Schliemann
> IT-Consultant
> ____________________________________________
> comundus IT-Beratung GmbH
> Schüttelgrabenring 3, 71332 Waiblingen
>
> Tel:    +49 (0) 71 51-5 00 28-0   Fax: -99
>
> www.comundus.com    www.comundus-e-quality.com
> ____________________________________________
>
>
>
> _______________________________________________
> This mail is send to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please 
> visit
> http://mail.opencms.org/mailman/listinfo/opencms-dev
>
>
--
Claus Priisholm, CodeDroids ApS
+45 48 22 46 46
email: cpr (you-know-what) interlet.dk - cpr (you-know-what) 
codedroids.com
skype: claus_priisholm
http://www.interlet.dk - http://www.codedroids.com