In Long vs. Integer we settled, once and for all, the debate over integer data types. But what about floating point? Pace recently asked which was better, single or double. (Pace: I know it was two months ago, but cut me some slack.) My gut reaction is that Double is the fastest. Excel stores it’s cell values as Double and any other data type would require some internal conversion. If you put a “1? in cell A1 and type
in the Immediate Window, you get Double as a result. That seems to indicate that cell values are stored as Double. InsaneExcel indicates that Doubles take longer than Singles, but they’re not converting cell values. I’ve never heard that VBA uses Double exclusively and converts other floating points, like it converts other integers to Long. But it seems that if you were reading from cell values, it would have to do some conversion.
There is a slight problem with this theory, however. If you put a string in A1, TypeName will return String as the data type. That indicates that the cell contents are really Variants. If that’s true, it shouldn’t take any longer to convert a Variant to a Single than it does to convert it to a Double, speaking as someone who wouldn’t know a pointer from a hole in his head.
Well, I decide to run this little test
Dim t As Long
Dim rCell As Range
Dim snTest As Single
Dim dTest As Double
Dim cTest As Currency
Application.Wait Now + TimeValue(“00:00:01”)
t = Timer
For Each rCell In ActiveSheet.Columns(1).Cells
snTest = rCell.Value
snTest = snTest * snTest
Next rCell
Debug.Print “Single”, Timer – t
Application.Wait Now + TimeValue(“00:00:01”)
t = Timer
For Each rCell In ActiveSheet.Columns(1).Cells
dTest = rCell.Value
dTest = dTest * dTest
Next rCell
Debug.Print “Double”, Timer – t
Application.Wait Now + TimeValue(“00:00:01”)
t = Timer
For Each rCell In ActiveSheet.Columns(1).Cells
cTest = rCell.Value
cTest = cTest * cTest
Next rCell
Debug.Print “Currency”, Timer – t
Debug.Print String(50, “-“)
End Sub
and I got this result
If I didn’t include Application.Wait, I got some screwy result. Currency almost always came up negative. Initially, I thought I discovered a way to travel back in time, but alas, it wasn’t so. What I did conclude, however, is that it doesn’t make a lick of difference whether I use Single or Double. Since Double has more capacity, there’s really no reason not to use it. One could make the same argument for Currency, but Currency is really a fixed point data type, not floating point. I only include it in the test because it can store large numbers. Besides, I’m really used to typing Double.
Does my test support that conclusion, or is there a flaw somewhere?
for a start I unsure as to the resolution (and accuarcy) of the VB Timer function.
Some quick tests with the API function QueryPerformanceCounter suggests the situation may be a bit more complicated. My setup was not exactly like yours, but I believe it conforms in spirit.
These results say that VBA arithmetic (ie, the snTest=snTest*snTest part) is about 10% faster with singles than doubles and about 20% faster with doubles than currencies. This makes sense to me.
Reading from the worksheet (ie, your snTest = rCell.Value part) is about 2% faster with singles than doubles and only slightly (0.5% or so) faster with doubles than currencies. Since read operations are about an order of magnitude slower than multiplication and division, the contribution to read-and-calculate comparisons could dominate the results.
And, finally, writing to the worksheet (ie, something like rCell.Value = snTest) does conform to prior expectations: doubles are slightly faster (about 1%) than singles and doubles are notable (about 10% faster) than with currencies. Furthermore, writing to the worksheet is another couple of orders of magnitude slower than reading from it.
All in all, I’d say that if the application is dominated by reading *and* writing, doubles may be optimal. However, if the application is dominated by calculations, then singles may actually be better.
Can’t comment on speed testing, but InsaneExcel has some interesting stuff!
1. What’s in column 1?
2. Notice the repeating values? How many times does 0.171020… appear? There is some kind of rounding thing going on, otherwise you’d expect the values to only center around such a value. Generally a timing routine will repeat an action 100 or 1000 times, then divide by the number of repetitions.
re. the Timer function, I seem to remember it has a resolution of 18.2 ticks per second (0.055)
When used to time code which executes quickly it is best to repeat the test procedure (e.g. 100 times, 1000 times etc.) until 0.055 seconds is insignificant in the result.
Hi all,
What is your hardware plateform ? Whatever the method you use you should give the hardware platform and mainly the cpu. As you may know AMD vs Intel core cpu may have important differences in calculating (interpreting) long/double.
Secondly, as Ross said, I would use the API function GetTickCount (which is known as better for time performance) – I don’t know about the API function QueryPerformanceCounter suggested by Keith Johnson.
Here’s a bit more of my take on floating point timing. I keep a workbook and/or code module with the following handy when speed matters. The subs Option1 and Option2 vary depending on what’s being tested. The initial loop in sub Test determines a sensible number of iterations to use (set so the test time will be around 0.5 – 5 seconds). The two timing loops differ only in whether Option1 or Option2 are called. This is a simple but useful setup. It’s all I usually employ; because, when timing differentials are not large (25% or more), I’d rather use cleaner code than faster. (And, yes, I almost always use doubles too.)
Private Declare Function GetTicks Lib “kernel32? Alias “QueryPerformanceCounter” (Tick As Currency) As Long
Private Function PerfTimer() As Double
Dim Tick As Currency
Call GetTicks(Tick)
PerfTimer = Tick / 357.9545@
End Function
Public Sub Test()
Dim nIter As Long, n1 As Long, n2 As Long
Dim x1 As Double, x2 As Double, x As Double
For n1 = 0 To 10
nIter = 10 ^ n1
x = PerfTimer()
For n2 = 1 To nIter
Call Option1
Call Option2
Next
x = PerfTimer() – x
Debug.Print “nIter = ” & Format(nIter, “#,##0?) & ” – Time = ” & Format(x, “0.000?)
If (x > 1) Then Exit For
Next
‘Option1 block
x1 = PerfTimer()
For n1 = 1 To nIter
Call Option1
Next
x1 = PerfTimer() – x1
Debug.Print “Option1 = ” & Format(x1, “0.00?) & “, scaled = 100.00?
‘Option2 block
x2 = PerfTimer()
For n1 = 1 To nIter
Call Option2
Next
x2 = PerfTimer() – x2
Debug.Print “Option2 = ” & Format(x2, “0.00?) & “, scaled = ” & Format(x2 / x1 * 100, “0.00?)
End Sub
In the floating-point case, however, timing differences are small. (I’m even partially convinced that ordering seems to matter, whether Option1 is called before Option2 or vice versa.) So I’ve repeated some of this analysis, a bit more carefully. Here’s Option1() for TestA — a “bare loop”:
Private Sub Option1()
Dim snTest As Double, rCell As Range
‘ snTest = 5
For Each rCell In ActiveSheet.Range(“Test”).Cells
‘ snTest = rCell.Value
‘ snTest = snTest * snTest / snTest * snTest / snTest * snTest / snTest * snTest / snTest
‘ rCell.Value = snTest
Next rCell
End Sub
Option2 is identical, except that snTest is a Single and in Option3 snTest is a Currency. TestB covers “arithmetic” and activates the lines snTest = 5 and snTest = snTest * snTest /… TestC covers “reads” with only the line snTest = rCell.value active. And TestD, with snTest = 5 and rCell.Value = snTest active.
The main sub Test was rewritten to turn off screen updating, reset calculation to manual (although that shouldn’t matter?), and to call the three subroutines in random order. Based on 100 replications, each using 100 iterations, the results are
Bare loop
Doubles0.0342 (.0009) seconds per 100 iterations
Singles0.0333 (.0008) // seems faster by 2.4%, but that’s less than one standard-deviation//
Currency0.0341 (.0010)
Arithmetic
Doubles0.0346 (.0009) seconds per 100 iterations
Singles0.0342 (.0008)
Currency0.0456 (.0011) // 32% slower; easily significant //
Reads
Doubles0.1392 (.0014) seconds per 100 iterations
Singles0.1383 (.0015)
Currency0.1395 (.0014)
Writes
Doubles0.8277 (.0028) seconds per 100 iterations
Singles0.8508 (.0027) // 2.8% slower, significant //
Currency1.0263 (.0070) // 24% slower, easily significant //
Given this, I can’t see any good reason to deviate from the practice of always using doubles. The results do suggest, however, that writes are way more important than reads in determining code speed — even when calculation and screen updating are turned off. This makes sense, but the magnitude of the difference surprised me.
This, by the way, is on a 2.53GHz Pentium 4, XP, and Excel 2002. My experience, of late, is that relative speeds are not so much hardware dependent — although absolute speeds certainly vary.
Keith
Hi all,
I believe Charles Williams add-in “High Resolution Timers” may be suitable to achieve a better accuracy: http://www.decisionmodels.com/downloads.htm
Kind regards,
Dennis
Isn’t all floating point math done in the floating point unit of the CPU? [And if not, why the heck not?] If so, there shouldn’t be any difference in calculation speed other than load and store operations. Since 32-bit singles are smaller than 64-bit doubles, it may take less time to load and store them.
That said, the extra precision doubles provide means most algorithms don’t need to be optimized for prevention of rounding error. Start using nothing but singles, and most VBA programmers would likely introduce difficult to diagnose numeric bugs. Wouldn’t seem to make sense to chase slight performance improvement.
I think we’re all on the same page. My results (the more careful ones — which, by the way, uses the “high resolution timer” Charles Williams used), InsaneExcel’s results, and fzz’s and everyone else’s intuition says that, although singles may be a bit faster than doubles, the performance gain is way to small to chase.
In addition, floating point arithmetic is noteably faster than read or write to Excel — which, of course, is not the same as load/store. I believe there is a small (but statistically significant) performance gain writing doubles instead of singles. Since writes are so time-consuming, this 2.8% improvement is equivalent to a 15% gain while reading and about a 70% math improvement. At least until someone proves otherwise, doesn’t this make using doubles almost a no-brainer?
Regards,
Keith
Most of my apps are highly user-interactive. Sure, I could save a few zillionths of a second using singles instead of doubles, but then the user scratches his head for ten minutes, and it hardly seems worth it. I just use doubles and longs, and dispense with singles and integers. Less for this old brain to keep up with, and anyway, it’s only VBA.
I’m a little surprised no one has mentioned VBA’s Decimal data type. In my line of work (modelling business entities) I very rarely encounter data that is genuinely floating-point in nature; in almost every case they are fixed-point decimals. Yet, I see the Double data type being widely used within my business domain, notably in VBA and Access/SQL Server, when the DECIMAL data type is required. Sure, I could spend my time proving that using a Double/FLOAT would produce zero errors based on the known data in the domain but why not just mirror the reality and model fixed-point decimals as fixed-point decimals?
Jamie.