On Tue, Jul 5, 2016 at 5:14 PM, Darren Duncan <dar...@darrenduncan.net> wrote:
> Ertan, > > You should read https://www.sqlite.org/datatype3.html#section_2 if you > haven't already. > > SQLite has exactly 1 floating-point type, which is what you get no matter > if you say float/double/etc, and it is 8 bytes, which corresponds to a > Double in most programming languages. > > the ALU on the CPU has exactly one type too - it uses double internally. C# has 'decimal' which is a fixed 4 decimal points(err I guess it's different) http://stackoverflow.com/questions/618535/difference-between-decimal-float-and-double-in-net but you're actually in delphi to the database so that doesn't help. It would be best to keep it in scaled precision of maximum places you want to keep ( pennies + 4 = 6 )... x * 1 000 000 0.97 * 10 != 0.97 + 0.97 + ... (10 times) ... ... if you count from 0.01 to 1.00' and test each one you'll find at least one that fails. But since SQL math is done as IEEE floating point it's not going to be the same result as the machine nessecarily. You can't change what SQLite uses other than explicitly not using a > floating-point type; you have integer, float, blob, text, and that's it. > > Therefore, if you use anything other than a double in your app, you will > be converting when storing or retrieving, which makes things slower and > introduces loss, hence I say use Double everywhere. > > -- Darren Duncan > > > On 2016-07-05 4:34 PM, Ertan Küçükoğlu wrote: > >> Hardware that the application will run is ~1Ghz Atom CPU, ~1GB RAM, >> regular Laptop HDD (no SSD). Time to time, there will be calculations, >> Network file transfer, and DLL function calls (all three) will be >> simultaneously running. Application has Timers and Socket listening >> components on it. My concern is to have a situation which "seems" like an >> application freeze. >> >> On the other hand, will it be any help to increase accuracy to use >> Currency (8 bytes fixed-point data type) in my application for Price and >> Total fields, leave Amount field as Double? >> >> Thanks. >> >> -----Original Message----- >> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto: >> sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan >> Sent: Wednesday, July 6, 2016 1:20 AM >> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >> Subject: Re: [sqlite] Currency & SQLite >> >> On 2016-07-05 3:02 PM, Ertan Küçükoğlu wrote: >> >>> I am using Delphi 10 for programming. Current variables are defined as >>> Double (8 bytes real), saved in Database using something >>> "DataSet.FieldByName('Amount').AsFloat := My_double_variable" However, I am >>> now considering to convert all Double datatypes to Single (4 bytes real) >>> for performance issues. >>> >> >> Do NOT change to using Single, always use Double, end to end. >> >> First of all, Double is what SQLite uses internally for floats, per its >> documentation: >> >> "The value is a floating point value, stored as an 8-byte IEEE >> floating point number." >> >> So if you're already using Double in your app then staying that way >> should maintain accuracy, especially when you want accuracy for more than >> say 3-5 decimal digits in total. >> >> Secondly, for financial applications, accuracy trumps performance. >> Converting to Single at any time is just going to cause you trouble. >> >> And really, are you sure using Single would actually help performance? >> Have you measured it in a real workload? More likely on modern devices it >> won't be any faster and you will have lost your accuracy for nothing. >> >> -- Darren Duncan >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
