 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / September 2005int Versus decimal
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 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.  SignatureAdam Machanic SQL Server MVP http://www.datamanipulation.net -- > Howdy - - > [quoted text clipped - 7 lines] > > - - Herb > 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 > 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. SignatureAdam Machanic SQL Server MVP http://www.datamanipulation.net -- |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.