Posted by shiju on 08/02/07 14:12

On Aug 2, 1:46 pm, David Greenberg <davi...@iba.org.il> wrote:
> Hi
> This is a question of "what does it cost me".
>
> Lets say I have an integer value which would fit into a smallint field
> but the field is actually defined as int or even larger as bigint.
> What would that "cost" me ? How would definitions larger than I need for
> the values in the field affect me ?
> Its obvious that the volume of the database would grow but with the size
> of resources etc that we have nowadays disc space isn't a problem like
> it used to be and i/o is much faster and many people would tell me "who
> cares" , or IS it a problem ?
> How does it affect performance of data retrieves ? Searches ? Updates
> and inserts ? How would it affect all db access if tables are pointing
> at each other with foreign keys ?
>
> Thanks !
>
> David Greenberg

Less data can fit in a page....This will degrade performance.

Let say we have a single column table which is BigInt which you could
have declared as INT

over the period the data grown up to several hundered pages....say
10000 pages

If you could have used int instead of bigint it woluld have only
consumed 5000 pages for the same amount of data.


This is about storage. and caching

Now abt the CPU.
suppose you run a sum() on a coloumn of bigint it will require more
than twice the time of CPU as of Int. as CPU has to manupulate 8 bytes
instead of 4 bytes.


So your select will be slow,update/insert will be slow (more chances
of page split). Delete will be slow.
cache hits will be low (as low page fit on Memory).
CPU consumption will be high.
Backup/Restore would have been faster with the less pages.
That all I can think of now there may be more downsides

Hope it helps
Thanks
Shiju Samuel

[Back to original message]