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

Double Byte Character Problem

I am storing text in sql server 2000. DataType for the field is ntext. I am
storing a string which has characters both in english and in Japanese. If the
number of characters in string are upto 4000 then every thing works fine. If
I retrieve the data it shows me exactly the string which I stored. But the
moment chracters exceed 4000(even 4001) the japanese characters are converted
to question mark("?") . Collation for the database is
SQL_Latin1_General_CP1_Cl_AS. Please help me.
Thank you.
Junaid Rehman
Are you observing this behavior from Query Analyzer or your application
program?
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>I am storing text in sql server 2000. DataType for the field is ntext. I am
> storing a string which has characters both in english and in Japanese. If
> the
> number of characters in string are upto 4000 then every thing works fine.
> If
> I retrieve the data it shows me exactly the string which I stored. But the
> moment chracters exceed 4000(even 4001) the japanese characters are
> converted
> to question mark("?") . Collation for the database is
> SQL_Latin1_General_CP1_Cl_AS. Please help me.
> Thank you.
> Junaid Rehman
|||I made a simple page in vb.net which has two textboxes and a submit button. I
enter the text in first textbox and when I submit the form data is inserted
into database. When the characters entered in textbox are less then 4000 the
data which is inserted in database has japanese characters represented as
small boxes. Then I retrieve the the data stored in database in 2nd textbox
and it appears fine. But when characters entered in first textbox exceed 4000
the data which is inserted into database has japanese characters converted to
question mark("?") instead of those small boxes (which were there earlier
when characters were less then 4000). When I retrieve this text in 2nd box it
shows question marks instead of Japanese characters. I explained it in bit
detail so that you know what I am exactly doing.
Junaid Rehman
"Dan Guzman" wrote:

> Are you observing this behavior from Query Analyzer or your application
> program?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>
>
|||Below is a code snippet that properly stores unicode values as described in
your narrative. Also, I used a font that properly rendered the unicode
characters. You might check this against your code. If you still have
problems, please post your code.
Dim mySqlConnection As New SqlConnection(connectionString)
mySqlConnection.Open()
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = mySqlConnection
'create test table
mySqlCommand.CommandText = "CREATE TABLE #MyTable(MyData ntext)"
mySqlCommand.ExecuteNonQuery()
'create unicode value from form
mySqlCommand.CommandText = "INSERT INTO #MyTable VALUES(@.MyData)"
Dim myDataParamter As New SqlParameter("@.MyData", SqlDbType.NText)
myDataParamter.Value = Me.TextBox1.Text
mySqlCommand.Parameters.Add(myDataParamter)
mySqlCommand.ExecuteNonQuery()
'retrieve inserted value
mySqlCommand.CommandText = "SELECT MyData FROM #MyTable"
Dim SqlDataReader As SqlDataReader = mySqlCommand.ExecuteReader
SqlDataReader.Read()
Me.TextBox2.Text = SqlDataReader.GetString(0)
SqlDataReader.Close()
'cleanup
mySqlCommand.CommandText = "DROP TABLE #MyTable"
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:6C3267D6-DD99-417A-9B65-D40FE6217DF2@.microsoft.com...[vbcol=seagreen]
>I made a simple page in vb.net which has two textboxes and a submit button.
>I
> enter the text in first textbox and when I submit the form data is
> inserted
> into database. When the characters entered in textbox are less then 4000
> the
> data which is inserted in database has japanese characters represented as
> small boxes. Then I retrieve the the data stored in database in 2nd
> textbox
> and it appears fine. But when characters entered in first textbox exceed
> 4000
> the data which is inserted into database has japanese characters converted
> to
> question mark("?") instead of those small boxes (which were there earlier
> when characters were less then 4000). When I retrieve this text in 2nd box
> it
> shows question marks instead of Japanese characters. I explained it in bit
> detail so that you know what I am exactly doing.
> Junaid Rehman
> "Dan Guzman" wrote:

Double Byte Character Problem

I am storing text in sql server 2000. DataType for the field is ntext. I am
storing a string which has characters both in english and in Japanese. If the
number of characters in string are upto 4000 then every thing works fine. If
I retrieve the data it shows me exactly the string which I stored. But the
moment chracters exceed 4000(even 4001) the japanese characters are converted
to question mark("?") . Collation for the database is
SQL_Latin1_General_CP1_Cl_AS. Please help me.
Thank you.
Junaid RehmanAre you observing this behavior from Query Analyzer or your application
program?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>I am storing text in sql server 2000. DataType for the field is ntext. I am
> storing a string which has characters both in english and in Japanese. If
> the
> number of characters in string are upto 4000 then every thing works fine.
> If
> I retrieve the data it shows me exactly the string which I stored. But the
> moment chracters exceed 4000(even 4001) the japanese characters are
> converted
> to question mark("?") . Collation for the database is
> SQL_Latin1_General_CP1_Cl_AS. Please help me.
> Thank you.
> Junaid Rehman|||I made a simple page in vb.net which has two textboxes and a submit button. I
enter the text in first textbox and when I submit the form data is inserted
into database. When the characters entered in textbox are less then 4000 the
data which is inserted in database has japanese characters represented as
small boxes. Then I retrieve the the data stored in database in 2nd textbox
and it appears fine. But when characters entered in first textbox exceed 4000
the data which is inserted into database has japanese characters converted to
question mark("?") instead of those small boxes (which were there earlier
when characters were less then 4000). When I retrieve this text in 2nd box it
shows question marks instead of Japanese characters. I explained it in bit
detail so that you know what I am exactly doing.
Junaid Rehman
"Dan Guzman" wrote:
> Are you observing this behavior from Query Analyzer or your application
> program?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
> >I am storing text in sql server 2000. DataType for the field is ntext. I am
> > storing a string which has characters both in english and in Japanese. If
> > the
> > number of characters in string are upto 4000 then every thing works fine.
> > If
> > I retrieve the data it shows me exactly the string which I stored. But the
> > moment chracters exceed 4000(even 4001) the japanese characters are
> > converted
> > to question mark("?") . Collation for the database is
> > SQL_Latin1_General_CP1_Cl_AS. Please help me.
> > Thank you.
> >
> > Junaid Rehman
>
>|||Below is a code snippet that properly stores unicode values as described in
your narrative. Also, I used a font that properly rendered the unicode
characters. You might check this against your code. If you still have
problems, please post your code.
Dim mySqlConnection As New SqlConnection(connectionString)
mySqlConnection.Open()
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = mySqlConnection
'create test table
mySqlCommand.CommandText = "CREATE TABLE #MyTable(MyData ntext)"
mySqlCommand.ExecuteNonQuery()
'create unicode value from form
mySqlCommand.CommandText = "INSERT INTO #MyTable VALUES(@.MyData)"
Dim myDataParamter As New SqlParameter("@.MyData", SqlDbType.NText)
myDataParamter.Value = Me.TextBox1.Text
mySqlCommand.Parameters.Add(myDataParamter)
mySqlCommand.ExecuteNonQuery()
'retrieve inserted value
mySqlCommand.CommandText = "SELECT MyData FROM #MyTable"
Dim SqlDataReader As SqlDataReader = mySqlCommand.ExecuteReader
SqlDataReader.Read()
Me.TextBox2.Text = SqlDataReader.GetString(0)
SqlDataReader.Close()
'cleanup
mySqlCommand.CommandText = "DROP TABLE #MyTable"
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:6C3267D6-DD99-417A-9B65-D40FE6217DF2@.microsoft.com...
>I made a simple page in vb.net which has two textboxes and a submit button.
>I
> enter the text in first textbox and when I submit the form data is
> inserted
> into database. When the characters entered in textbox are less then 4000
> the
> data which is inserted in database has japanese characters represented as
> small boxes. Then I retrieve the the data stored in database in 2nd
> textbox
> and it appears fine. But when characters entered in first textbox exceed
> 4000
> the data which is inserted into database has japanese characters converted
> to
> question mark("?") instead of those small boxes (which were there earlier
> when characters were less then 4000). When I retrieve this text in 2nd box
> it
> shows question marks instead of Japanese characters. I explained it in bit
> detail so that you know what I am exactly doing.
> Junaid Rehman
> "Dan Guzman" wrote:
>> Are you observing this behavior from Query Analyzer or your application
>> program?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
>> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>> >I am storing text in sql server 2000. DataType for the field is ntext. I
>> >am
>> > storing a string which has characters both in english and in Japanese.
>> > If
>> > the
>> > number of characters in string are upto 4000 then every thing works
>> > fine.
>> > If
>> > I retrieve the data it shows me exactly the string which I stored. But
>> > the
>> > moment chracters exceed 4000(even 4001) the japanese characters are
>> > converted
>> > to question mark("?") . Collation for the database is
>> > SQL_Latin1_General_CP1_Cl_AS. Please help me.
>> > Thank you.
>> >
>> > Junaid Rehman
>>