Single or Double

[James Martin]

I'm supporting a database that is comprised largely of fields and variables
with the data type Single. Over the years, the client's needs have changed,
so occasionally I have to change some of these fields and variables to
Double. This doesn't happen often, but once in a while, calculations will be
a little off and it will be because they are getting larger values in their
raw data than they ever anticipated. When this happens, changing the
relevant fields and associated variables to Double has solved the problem.

I'm considering just changing everything from Single to Double to prevent
more of these kinds of problems in the future. For that matter, I'm
considering only using Double from now on in any new database, just to avoid
the kind of minute precision errors that occasionally crop-up with Single
when raw data is unanticipatedly small or large.

Is there any compelling reason to use Single anymore? I realize that at one
time, memory concerns might have been an issue. But is there a good reason
to use Single today? Specifically, I'm wondering if there is any performance
difference between the two.

Thanks in advance.

James

 

[Jerry Whittle]

Way back in the day (like Access 1 where an MDB file could only be 100 MB and
a 1 GB hard drive was expensive) it paid to chose the smallest datatype that
would work. Now with 2GB files and cheap multi-GB drives, it's really not a
concern.

I usually set the default to double on my databases. I may change things to
a long integer, decimal, or even currency as required, but start out with
doubles.

 

[James Martin]

Thanks, Jerry. That's very good news. So I shouldn't expect the database's
performance to degrade if I convert all the singles to doubles? That's the
main thing that has been preventing me from making the change.

James

 

[Jeff Boyce]

James

How many "decimal places" are commonly found in your clients' data?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Allen Browne]

Do some comparison timing with the data you are typically talking about and
see if you can measure any difference.

My guess is that any performance difference will be minimal.

My practice is to always use a Double, because (as you found) accuracy is
more important than performance anyway.

While Double has more precision than single, any floating point time still
cannot display fractional numbers precisely:
http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems

 

[James Martin]

Anywhere from 0 to 4, although most of the data is to 3 decimal places.

 

[Jeff Boyce]

James

If your clients will never have more than 4 decimal places, consider using
the Currency datatype instead of Single or Double. The Single and Double
datatypes are stored as binary values, where the Currency datatype is stored
as the value exactly as entered.

You will encounter rounding and other math "errors" using the Single and
Double types that you won't if you use Currency (which has a maximum of 4
decimal places!).

Or am I missing something...?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[James Martin]

Hi Jeff,

That's an excellent idea. The grief I've been going through to prevent
rounding errors is remarkable. It's made me long for the accurate of 1970's
calculators.

One last question about the Currency datatype. The application does a lot of
calculations. Do you have any idea what the relative performace and
efficiency of Currency, Single, and Double is like?

James

 

[Jeff Boyce]

James

I don't have any "metrics" at hand on relative performance. Is this
"prurient interest" or does your application have performance issues?

If there are "a lot of calculations", have you considered exporting the
(initial) raw data to a more suitable calculation engine (e.g., Excel)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[James Martin]

Jeff,

Although most of the application has no performance issues, there are two
update routines and one report that are slow enough to be somewhat
frustrating. If they were to slow down any more, I'd have to enter the
Witness Protection Program.

But I'll try switching them and run the report. That would give me a pretty
quick idea what (if any) performance effect this has.

Thanks again.

James

 

[Allen Browne]

James, let us know what you find, but I'd be surprised if there were much
measurable difference.