Re: Use of large field definitions for small values
- From: shiju <shiju.samuel@xxxxxxxxx>
- Date: Thu, 02 Aug 2007 07:12:03 -0700
On Aug 2, 1:46 pm, David Greenberg <davi...@xxxxxxxxxx> 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
.
- References:
- Use of large field definitions for small values
- From: David Greenberg
- Use of large field definitions for small values
- Prev by Date: Examination 070-229 Resource
- Next by Date: Re: Numeric value only for a character field
- Previous by thread: Use of large field definitions for small values
- Next by thread: Re: Use of large field definitions for small values
- Index(es):
Relevant Pages
|
