double vs decimal in high-frequency db

I am storing financial data (stock prices, profit and loss figures, etc..) i
n
a SQL 2005 database. The database will be updated with a high frequency. I a
m
debating whether to use decimal or double/float to store these values and I
am concerned that using decimal will slow down the database. Several
questions:
If I am updating the db with prices at 1000 times/second, will there be a
significant performance difference if I use decimal to store the prices?
If I am performing calculations on another table with the P&L data using a
stored procedure at 1 time/ second, will there be a significant performance
difference if I use decimal to store the P&L data?
If I went with double over decimal, can I round after the calculation? Will
that be faster than just using decimal?
Thank you,
JasonCP Developer wrote:
> I am storing financial data (stock prices, profit and loss figures, etc..)
in
> a SQL 2005 database. The database will be updated with a high frequency. I
am
> debating whether to use decimal or double/float to store these values and
I
> am concerned that using decimal will slow down the database. Several
> questions:
> If I am updating the db with prices at 1000 times/second, will there be a
> significant performance difference if I use decimal to store the prices?
> If I am performing calculations on another table with the P&L data using a
> stored procedure at 1 time/ second, will there be a significant performanc
e
> difference if I use decimal to store the P&L data?
> If I went with double over decimal, can I round after the calculation? Wil
l
> that be faster than just using decimal?
> Thank you,
> Jason
There is no built-in type called DOUBLE.
It's certainly possible that the datatype will affect performance but
that's something best tested for yourself with your own data and
queries.
FLOAT is surely going to be less suitable for financial data because
it's an inexact numeric. You'll lose decimal precision that way. My
inclination would be to choose DECIMAL unless you find a compelling
reason to do otherwise.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David Portas wrote:
> FLOAT is surely going to be less suitable for financial data because
> it's an inexact numeric. You'll lose decimal precision that way. My
> inclination would be to choose DECIMAL unless you find a compelling
> reason to do otherwise.
Just out of interest, what would be a situation where FLOAT would be
preferable to DECIMAL?|||I think you are getting the cart before the horse. First define the
data requirement, then choose the data type among those that fit the
requirement.
FLOAT is imprecise for storing decimal fractions - read the Books on
Line. It seems unlikely that "pretty close" is sufficient for
financial data, but you are the one who has to make that
determination. Only after you are certain that such imprecision is of
no consequence to the system can questions of performance be a factor.
That said, I do not believe you will have a measureable difference
either way. When taken as a percentage of the overall processing to
perform an UPDATE, the difference will probably not be noticeable.
But just to keep me honest, I just set up two tables of over 1 million
rows each (1048576 to be precise). One has a column declared FLOAT,
the other has one declared DECIMAL(30,10). I ran mass UPDATEs against
both tables, with "SET nbr = nbr * 1.111" as the change. The
estimated execution plans for the two updates favored the FLOAT
version 46.27% against 53.73% for the DECIMAL. And execution times
for the 1048576 updates were consistently 5 seconds for FLOAT and 6
seconds for DECIMAL.
Roy Harvey
Beacon Falls, CT
On Wed, 31 May 2006 14:46:02 -0700, CP Developer
<steved@.newsgroup.nospam> wrote:

>I am storing financial data (stock prices, profit and loss figures, etc..)
in
>a SQL 2005 database. The database will be updated with a high frequency. I
am
>debating whether to use decimal or double/float to store these values and I
>am concerned that using decimal will slow down the database. Several
>questions:
>If I am updating the db with prices at 1000 times/second, will there be a
>significant performance difference if I use decimal to store the prices?
>If I am performing calculations on another table with the P&L data using a
>stored procedure at 1 time/ second, will there be a significant performance
>difference if I use decimal to store the P&L data?
>If I went with double over decimal, can I round after the calculation? Will
>that be faster than just using decimal?
>Thank you,
>Jason|||CP Developer (steved@.newsgroup.nospam) writes:
> I am storing financial data (stock prices, profit and loss figures,
> etc..) in a SQL 2005 database. The database will be updated with a high
> frequency. I am debating whether to use decimal or double/float to store
> these values and I am concerned that using decimal will slow down the
> database. Several questions:
> If I am updating the db with prices at 1000 times/second, will there be a
> significant performance difference if I use decimal to store the prices?
As long as you are only storing the data, I cannot see that it matters.
But the problem with decimal is that you have to decide for a number
of decimals. Say that you settle for six decimals. You can bet that
one day there is a fund which is quoted in eight decimals.

> If I am performing calculations on another table with the P&L data using
> a stored procedure at 1 time/ second, will there be a significant
> performance difference if I use decimal to store the P&L data?
The only way to find out, is to benchmark.

> If I went with double over decimal, can I round after the calculation?
> Will that be faster than just using decimal?
Float sounds more appealing to me, for the simple reason that you will
have less problem with losing precision in intermediate results.
Yes, you can round float values, but keep in mind that an exact number
with three decimals, say 7.234 cannot be exactly represented in float,
so that you in reality have something like 7.2340000000000000001 or
7.23399999999999999989 depening on how you arrived to that number.
But all that is manageable.
I work with system for securities trading, including P%L computation,
and we use float for prices, amounts, quantities etc. You have to under-
stand the pitfalls, but after all these years, I think I do. I have
less feeling for the pitfalls with decimals, so I don't think I want
to change.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 31 May 2006 15:21:42 -0700, Chris Lim wrote:

>David Portas wrote:
>Just out of interest, what would be a situation where FLOAT would be
>preferable to DECIMAL?
Hi Chris,
Usually in scientific calculations.
Hugo Kornelis, SQL Server MVP