|
Home > Archive > MS SQL Data Warehousing > September 2005 > int Versus decimal
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
int Versus decimal
|
|
| Herb Ray 2005-09-15, 11:23 am |
| Howdy - -
I have to keep time series on large amounts of numeric data (millions
of rows). I had the idea of storing these data as int and converting to
decimal in views; this way, my numeric columns shrink down to four bytes
from 9 bytes.
Would the performance gained be an improvement even with the conversion
overhead of the view?
- - Herb
| |
| Adam Machanic 2005-09-15, 1:23 pm |
| INT only uses 4 bytes because it doesn't have the precision that DECIMAL
has. How are you hoping to maintain the same precision when converting back
and forth?
By the way, if your column uses a precision up to 9 digits (decimal), it
will only use 5 bytes.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Herb Ray" <herb@ad-co.com> wrote in message
news:JzfWe.170$Zl2.10211487@news.sisna.com...
> Howdy - -
>
> I have to keep time series on large amounts of numeric data (millions
> of rows). I had the idea of storing these data as int and converting to
> decimal in views; this way, my numeric columns shrink down to four bytes
> from 9 bytes.
>
> Would the performance gained be an improvement even with the conversion
> overhead of the view?
>
> - - Herb
| |
| Herb Ray 2005-09-15, 1:23 pm |
| Adam Machanic wrote:
> INT only uses 4 bytes because it doesn't have the precision that DECIMAL
> has. How are you hoping to maintain the same precision when converting back
> and forth?
>
> By the way, if your column uses a precision up to 9 digits (decimal), it
> will only use 5 bytes.
>
>
Adam - -
Actuall, you're right. But, I do have data where smallint gives me
sufficient precision, so I would be storing two bytes instead of five
bytes for decimal. Is it worth it to convert in the view?
- - Herb
| |
| Adam Machanic 2005-09-15, 1:23 pm |
| "Herb Ray" <herb@ad-co.com> wrote in message
news:0yhWe.177$044.10674339@news.sisna.com...
>
> Actuall, you're right. But, I do have data where smallint gives me
> sufficient precision, so I would be storing two bytes instead of five
> bytes for decimal. Is it worth it to convert in the view?
You'd have to test on your end, with your data, to make that
determination. But my gut instinct would be probably not. That's a pretty
small difference in size, and although it might add up substantially over
billions of rows and give an I/O benefit in that case, I don't think it
would work in the millions (you're talking about a only few megs difference
on that scale.) There is also the overhead of the type conversion, which
may be expensive over so many rows -- but as to whether it would defeat the
possible I/O benefit, I cannot say; I think it would depend on many factors,
including how CPU-bound your servers are, and how fast your disks are.
--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
|
|
|
|
|