Quick Aside: I'm going to use the word "Float" to refer to
both a .Net float and a SQL float with only 7 significant digits. I will use the word
"Double" to refer to a .Net double and a SQL float with 15 significant digits. I also
realize that this is very similar to some other posts regarding decimals/doubles, but
the answers on those posts are really inconsistent, and I really want some
recommendations for my specific
circumstance...
I am part of a team that is
rewriting an old application. The original app used floats (7 digits). This of course
caused issues since the app conducted a lot of calculations and rounding errors
accumulated very quickly. At some point, many of these floats were changed to decimals.
Later, the floats (7) in the database all became doubles (15). After that we had several
more errors with calculations involving doubles, and they too were changed to
decimals.
Today about 1/3 of all of our floating
point numbers in the database are decimals, the rest are doubles. My team wants to
"standardize" all of our floating-point numbers in the database (and the new .Net code)
to use either exclusively decimals or doubles except in cases where the other MUST be
used. The majority of the team is set on using decimals; I'm the only person on my team
advocating using doubles instead of decimals. Here's
why...
- Most of
the numbers in the database are still doubles (though much of the application code still
uses floats), and it would be a lot more effort to change all of the floats/doubles to
decimals - For our app, none of the fields stored are
"exact" decimal quantities. None of them are monetary quantities, and most represent
some sort of "natural" measurement (e.g. mass, length, volume, etc.), so a double's 16
significant digits are already way more precise than even our initial measurements.
- Many tables have measurements stored in two columns: 1
for the value; 1 for the unit of measure. This can lead to a HUGE difference in scale
between the values in a single column. For example, one column can store a value in
terms of pCi/g or Ci/m3 (1 Ci = 1000000000000 pCi). Since all the values in a single
decimal columns must have the same scale (that is... an allocated
number of digits both before and after the decimal point), I'm concerned that we will
have overflow and rounding
issues.
My teammates
argue that:
- Doubles are
not as accurate nor as precise as decimals due
to their inability to exactly represent 1/10 and that they
only have 16 significant
digits. - Even though we are not tracking money, the app is
a inventory system that keeps track of material (mostly gram quantities) and it needs to
be "as accurate as possible". - Even after the
floats were changed to doubles, we continued to have bad results from calculations that
used doubles. Changing these columns (and the application code) to decimals caused these
calculations to produce the expected
results.
It is my strong
belief that the original issues where caused due to floats only having 7 significant
digits and that simple arithmetic (e.g. 10001 * 10001) caused them to the data to
quickly use up the few significant digits that they had. I do not believe this had
anything to do with how binary-floating point numbers can only
approximate decimal values, and I believe that using doubles would
have fixed this issue.
I believe that the issue
with doubles arose because doubles were used along side decimals in calculations that
values were be converted back and forth between data types. Many of these calculations
would round between intermediary steps in the
calculation!
I'm trying to convince my team not
to make everything under the sun into a decimal. Most values in the database don't have
more than 5 or 6 significant digits anyway. Unfortunately, I am out-ranked by other
members of my team that see things rather
differently.
So, my question then
is...
- Am I
worrying over nothing? Is there any real harm done by using almost exclusively decimals
instead of doubles in an application with nearly 200 database tables, hundreds of
transactions, and a rewrite schedule of 5 to 6 years? - Is
using decimals actually solving an issue that doubles could not? From my research, both
decimals and doubles are susceptible to rounding errors involving arbitrary fractions
(adding 1/3 for example) and that the only way to account for this is to consider any
value within a certain tolerance as being "equal" when comparing doubles and/or
decimals. - If it is more appropriate to use doubles, what
arguments could I make (other than what I have already made) could convince my team to
not change everything to
decimals?
No comments:
Post a Comment