Vb.net + SQL Server : Speed difference using float vs Decimal

Question

I'm using VB.net 2013 and SQL server 2008R2.

I'm writing a financial application , and I'm not sure about which data type to use.

I know that decimal is more precise than float , but I read that using decimal can significantly decrease the speed of calculations , so the speed of my application. I read that using decimals can be 20 times slower than using float. Is this true , and if yes is there a solution or should I continue to use float ?

Thank you !

You are looking at performance the wrong way. Start with "how fast does this button click need to be", then work backwards to see what options you have to achieve it. — Jonathan Allen, Sep 17 '14 at 22:36
Also, other factors such as whether or not you use an ORM will have a much greater impact than the data type. — Jonathan Allen, Sep 17 '14 at 22:37
@JonathanAllen: You can elaborate on "other factors" in your answer. Even though the topic is possibly too broad for SO, it would still be useful I think. — Victor Zakharov, Sep 17 '14 at 22:41
[Which is faster?](http://ericlippert.com/2012/12/17/performance-rant/) — Ňɏssa Pøngjǣrdenlarp, Sep 17 '14 at 22:53
But , is true that calculations with decimal are 20 time slower than calculations with float ? — alex, Sep 17 '14 at 23:04
By default ORMs do things that databases hate, like selecting every column in the table when you only need two or three. You can also lose performance by not having the right indexes (too few or too many). Making several round trips to the database instead of doing the work on the server using stored procedures can also be costly. — Jonathan Allen, Sep 18 '14 at 00:07

score 4 · Answer 1 · edited May 23 '17 at 11:56

4

Use decimal for financial applications, period. Speed does not matter in this case. When money is lost, your users won't be happy. You cannot argue saying "well, but on the other hand, it was fast". Regarding 20 times difference on float vs decimal, trust me, you won't feel it at all, there will be more major factors in your app's performance. Most likely trying to synchronize transactions between each other, DB locks etc.

EDIT: Regarding 20 times performance difference, this is true, I was able to reproduce with below code:

Module Module1

  Sub Main()
    Dim f As Single = 123456792.0F
    Dim fsw As New Stopwatch
    fsw.Start()
    For i = 1 To 100000000
      f *= 1.00000012F
    Next
    fsw.Stop()

    Dim dsw As New Stopwatch
    dsw.Start()
    Dim d As Decimal = 123456792.0F
    For i = 1 To 100000000
      d *= 1.00000012F
    Next
    dsw.Stop()

    Console.WriteLine(f)
    Console.WriteLine("Float (ms): " & fsw.ElapsedMilliseconds)
    Console.WriteLine(d)
    Console.WriteLine("Decimal (ms): " & dsw.ElapsedMilliseconds)
    Console.WriteLine("Float is " & dsw.ElapsedMilliseconds / fsw.ElapsedMilliseconds & " faster")

    Console.ReadLine()
  End Sub

End Module

Output:

enter image description here

While writing this code, I got anywhere between 10-20 times for different numbers. As I mentioned before, speed is not the concern, compare the accuracy of both approaches, notice how float is off by several orders of magnitude. This is, of course, a synthetic example, but it shows how people may end up with 1 dollar on their payroll instead of a 1000 - imagine the reaction.

edited May 23 '17 at 11:56

Community

1
1

answered Sep 17 '14 at 22:33

Victor Zakharov

25,801
18
85
151

But , is true that calculations with decimal are 20 time slower than calculations with float ? – alex Sep 17 '14 at 23:02
@alex: I doubt there is such a big difference, maybe 2 times, but I will do some research on this part now. Stay tuned. And yes, I found [where 20 times is coming from](http://stackoverflow.com/a/618542/897326). My 2 times are coming from the fact that Decimal use 128bits, and modern OSes are 64bit, so even though Float use 32bits, it's emulated via 64bit. – Victor Zakharov Sep 17 '14 at 23:25
Shouldn't you use `1.00000012M` for the second case? – Jonathan Allen Sep 18 '14 at 00:09
@JonathanAllen: [You mean 1.00000012D](http://stackoverflow.com/a/5489367/897326)? The idea was that I am passing the exact same number to be used in both calculations. I am after the visual effect here. Everyone call tell numbers are the same, because they are, well, identical, character by character. – Victor Zakharov Sep 18 '14 at 00:52
1

@alex Always use Decimal when dealing with money. Decimal is always the right tool for the job when it comes to finance and large math numbers. Depending on how complex the calculation, I have seen floats throw off numbers quite easily. – Jason Bayldon Sep 18 '14 at 03:31
I read in this article : http://support.microsoft.com/kb/78113 that Microsoft Excel use float in the calculations. But Excel is mostly an financial application. Why ? – alex Sep 18 '14 at 23:18
@alex: Don't forget that Double is also a floating point type, while Single is what is typically referred to as float. This can create confusion. Double has a lot more precision than Single, because it's two times more bits to store the number. If you are using Double, you are much less likely to encounter issues, but you may still encounter them at some point. Decimal won't calculate in this case. I don't consider Excel to be a financial application. Yes, it can be used for basic financial needs of a home business, perhaps. Anything larger than that would usually use QuickBooks or similar. – Victor Zakharov Sep 19 '14 at 01:54
@alex: Regarding why Excel implements it using float is because C++ does not have a decimal type, from what I know. Excel was originally written in C++, now it may be a combination of languages, but you get the point. – Victor Zakharov Sep 19 '14 at 01:56
ok , but in my application , using floats , I get a wrong result in this calculation : 29999.99-30000. I try in Excel and the result is exact. – alex Sep 19 '14 at 02:20
@alex: Are you using Single or Double in your application? – Victor Zakharov Sep 19 '14 at 02:34
in sql server the 2 fields are configured as real , so I read them in a single and I try this calculation – alex Sep 19 '14 at 02:41
@alex: [real is 4 bytes in SQL](http://msdn.microsoft.com/en-ca/library/ms173773.aspx) = Single in .NET, so that's why you may be getting an incorrect answer. – Victor Zakharov Sep 19 '14 at 11:03

Vb.net + SQL Server : Speed difference using float vs Decimal

1 Answers1