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

["Madl Alfred - Together AT" <A.Madl@xxxxxxxxxxx>]

Hi !

These Datatypes are mostly used for backward compatibility. We started 
discussing 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 
keys and foreign keys (single and multicolumn) instead of oids, pessimistic 
locking vs. optimistic version/timestamp vs. "no concurreny checking", 
mapping of referential integrity rules between DB and code, 
inheritance/aggregation, 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ão Paulo Ribeiro [mailto:jp@xxxxxxxxxxxx] 
Posted At: Montag, 26. September 2005 12:23
Posted To: Enhydra
Conversation: [enhydra] Why not bigint instead of numeric(19,0) in postgresql 
and using Long instead of BigDecimal
Subject: [enhydra] Why not bigint instead of numeric(19,0) in postgresql and 
using Long instead of BigDecimal


Hi!

This a an old question but i never got a good answer for this: why to use 
BigDecimal/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 
(objectids cant be negative)  =  10000000000000000000 BigInt is mapped to 
int8 - allow 2^63 positives values = 9223372036854780000 (just 8% less values 
than decimal(19,0)).

Ok, decimal (19,0) is bigger but BigInt should be big enough: assuming we use 
1 billion (1 000 000 000) oids per second, if someone is  using more than 
that let me know ;), we have  2^63/ (1 000 000 000 * 3600 * 24 *365) = 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 
virtually 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ão Paulo Ribeiro

-- 
João Paulo Ribeiro | Senior Software Engineer
jp@xxxxxxxxxxxx

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, is 
confidential and intended exclusively for the individual(s) named as 
addressees. 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 
destruction of the message, thereby notifying the sender.
DISCLAIMER: The sender of this message can not ensure the security of its 
electronic transmission and consequently does not accept liability for any 
fact which may interfere with the integrity of its content.