ObjectWeb Consortium
Search ObjectWeb Mail Archive: 

Advanced Search - Powered by Google


Mail Archive Home | enhydra List | September 2005 Index

<--  Date Index  --> <--  Thread Index  -->

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.




<--  Date Index  --> <--  Thread Index  -->

Reply via email to:

Powered by MHonArc.

Copyright © 1999-2005, ObjectWeb Consortium | contact | webmaster.