RE: [enhydra] Why not bigint instead of numeric(19,0) in postgresql and using Long instead of BigDecimal

This is a multi-part message in MIME format...

------------=_1127741712-2167-131
content-class: urn:content-classes:message
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi !

These Datatypes are mostly used for backward compatibility. We started disc=
ussing extensions of DODS to be able to use:

Different key mechanisms like oid per db-group, oid per db, oid per table, =
different datatypes for oids and version, timestamps instead of "version", =
"IDENTITY" db-vendor specific columns for oid's, user/application-defined k=
eys and foreign keys (single and multicolumn) instead of oids, pessimistic =
locking vs. optimistic version/timestamp vs. "no concurreny checking", mapp=
ing of referential integrity rules between DB and code, inheritance/aggrega=
tion, dynamic datamodels changed at runtime, etc.

Some of these new DODS features can be expected for DODS 7.0 with Enhydra 7=
.0. Enhydra 7.0 will have AMD64/EM64T support (main feature of this release=
) for all components like Installer, Director, Service wrapper, NT logging,=
 EnTray, etc. and ca be expected for Q1 or Q2 2006.

Of course if we can get some sponsoring for new features things can be done=
 with higher priority !

Greetings.

Alfred Madl



-----Original Message-----
From: Jo=E3o Paulo Ribeiro [mailto:[email protected]]=20
Posted At: Montag, 26. September 2005 12:23
Posted To: Enhydra
Conversation: [enhydra] Why not bigint instead of numeric(19,0) in postgres=
ql and using Long instead of BigDecimal
Subject: [enhydra] Why not bigint instead of numeric(19,0) in postgresql an=
d using Long instead of BigDecimal


Hi!

This a an old question but i never got a good answer for this: why to use B=
igDecimal/Decimal(19,0) instead of Long/BigInt?

1) Why to use Decimal(19,0) instead of BigInt?

1.1) BigInt has a range of values big enough
Decimal(19,0) is mapped to numeric (19,0) allow 10^19 positives values (obj=
ectids cant be negative)  =3D  10000000000000000000 BigInt is mapped to int=
8 - allow 2^63 positives values =3D 9223372036854780000 (just 8% less value=
s than decimal(19,0)).

Ok, decimal (19,0) is bigger but BigInt should be big enough: assuming we u=
se 1 billion (1 000 000 000) oids per second, if someone is  using more tha=
n that let me know ;), we have  2^63/ (1 000 000 000 * 3600 * 24 *365) =3D =
292 years of oids. It should be enough. :)

1.2) BigInt use less memory than Decimal(19,0) in db
int8 use less memory than Decimal(19,0) in db, the result is smaller object=
 in disk and ram. I made a few test and an index in a column type int8 use =
20% (average) less memory  pages: less resources needed and faster lookup.

1.3) Long is faster and smaller
Yes, the problem is the same. The BigDecimal has a range of values that vir=
tually unlimited, but assuming that you are working with
decimal(19,0) or int8 at db, the Long object is enough.

The Long object is much smaller: i did some tests and the Long is at least =
4 times smaller than the BigDecimal. The comparisons, for cache lookup are =
faster with Long as key than with BigDecimal.

We are using Enhydra and we have applications at production state that work=
 on tables with more than 27 millions rows ( big tables, not huge tables). =
This change can make a lot of difference in memory footprint and speed both=
 at the application server and the db server.

Then, i need to know why the default is BigDecimal/Decimal(19,0) instead of=
 Long/int8? There is some kind of retro-compatibility problem?

Best regards.
Jo=E3o Paulo Ribeiro

--=20
Jo=E3o Paulo Ribeiro | Senior Software Engineer
[email protected]

PHONE: + 351 253 305 250
FAX  : + 351 253 305 250
www.mobicomp.com

________________________________________________________________

About Solutions | Wireless World

CONFIDENTIALITY NOTICE: This message, as well as existing attached files, i=
s confidential and intended exclusively for the individual(s) named as addr=
essees. If you are not the intended recipient, you are kindly requested not=
 to make any use whatsoever of its contents and to proceed to the destructi=
on of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its e=
lectronic transmission and consequently does not accept liability for any f=
act which may interfere with the integrity of its content.




------------=_1127741712-2167-131
Content-Type: text/plain; name="message-footer.txt"
Content-Disposition: inline; filename="message-footer.txt"
Content-Transfer-Encoding: 8bit


--
You receive this message as a subscriber of the [email protected] mailing list.
To unsubscribe: mailto:[email protected]
For general help: mailto:[email protected]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

------------=_1127741712-2167-131--