|
Hi, I'm designing a C# app which will use a DB (probably SQL Server) for storing financial market historical data. I need some advice on data types:
Precision required is 7 digits like ##,###.## (e.g. Dow Jones index values).
for C#:
.) float (System.Single): needs 4 bytes - should be faster and more efficient than
.) double?
(Decimal with its 32 bytes would be extreme overkill and very slow, I guess.)
for Database - MSDN T-SQL tells:
.) real = float(24): needs 4 storage bytes, has 7 digits precision.
.) decimal(p,s): precision 1-9 needs 5 storage bytes
Now, would "float" with C# and "real" with SQL Server be best for my needs in terms of storage space and speed?
THX!
|
|
|
|
|
|
Yes, I do consider rounding errors.
But in this special case 7 digits are sufficient - therefore float and real should be ok technically? I know that I won't need more digits because my financial market data feed won't deliver more than these digits anyway, there are no additional floating point operations to be done other than just storing the numbers into database. The program won't need currency symbols or any other features used for accounting etc.
Mostly I'd like to know whether SQL Server data type "decimal(7,2)" or "real" is faster because an extra byte could make a storage difference with huge amounts of data.
Any ideas?
|
|
|
|
|
It's not that simple. While you will have enough 'digits' to store the value in a float since floats are stored in binary format the exact values they store will not map 1:1 onto the range of base10 decimals you want. For example .1(dec) is a repeating decimal in binary and consequently cannot be stored accurately.
IF you want to avoid the performance hit of the Decimal type you could use a fixed point integer. IE store the numbers in a 32bit it, but record $2.34 as 234. If you need to multiply or divide two fixed point numbers, however you'll need to implement additional logic in the calculation because the language you're writing in won't know that 200*200 is realy 2.00*2.00 and the correct answer is 400 not 40000.
The only way to enforce this would be to write Multiply and Divide functions and always use them instead of */ to do the computations. Doing this will greatly increase the chance of bugs however since it only takes one case of using the base operator to screw your numbers up.
Despite the performance hit, you're probably better off using the Decimal type because it hides all the ugly details from you.
I'd strongly advise you at least glance over the start of the article "What every computer scientist should know about floating point arithmatic". While it is rather dense it provides clear explanation of what actually goes on behind the scenes with float and double values and explains why they won't work the way you're niavely assuming.
http://docs.sun.com/source/806-3568/ncg_goldberg.html
|
|
|
|
|
Thx for your contribution.
Now of course floating point types differ a lot from "modern" base 10 types. I still remember the pain to fiddle around with bits for mantissa and exponent at university having to do such calculations manually on paper.
The point for my application is however, that unimportant digits won't matter at all. I could even discard everything from the 6th digit or so, it's the percentage of the values that counts.
E.g. when (12,345.67 + 1) turns out to be like 12,346.669998 - no problem whatsoever.
Preliminary tests seem to be ok and if I won't be satisfied with float, I'm sure I'd go for double instead. Tests for double show, they maintain all of my 7 digits precicely even after some calculations. There's just no way I'm touching those slow CPU-hardware-unsupported decimals when I don't need them at all.
I guess I still belong to the ol' bunch of guys who go for speed and efficiency even if computers are extremly fast these days...
|
|
|
|
|
I really think you should reconsider. If there's one case where you end up doing enough operations to the point that the errors do become detectable and the auditors find it you've got a major problem. Or even not a single case, but a consistant shift one direction propagated over thousands of calculations until it becomes visible.
|
|
|
|
|
By the way, here's a good explanation why I want to avoid .NET-decimal, taken from:
http://www.extremeoptimization.com/resources/articles/fpdotnetconceptsandformats.aspx
What about the decimal format?
The Decimal type in the .NET framework is a non-standard floating-point type with base 10. It takes up 128 bits. 96 of those are used for the mantissa. 1 bit is used for the sign, and 5 bits are used for the exponent, which can range from 0 to 28. The format does not follow any existing or planned standard. There are no infinities or NaN's.
Any decimal number of no more than 28 digits before and/or after the decimal point can be represented exactly. This is great for financial calculations, but comes at a significant cost. Calculating with decimals is an order of magnitude slower  than the intrinsic floating point types. Decimals also take up at least twice as much memory.
|
|
|
|
|
Hi community!
I was wondering if anyone has experienced a similar behaviour and knows how to fix it...
Recently I was told that a DB app I've written some time ago (using an Access 2000 DB) is showing entries that were deleted some time ago.
Another, perhaps connected behaviour report came from another customer, where I was told that some database entries were not visible for several hours. The INSERTs ran OK, as the log files prove, but querying the data from a client program didn't show the new entries (there were no error messages or anything, just an empty DataSet. Don't have client side logs, though.). I couldn't reproduce the behaviour, though, and the customer can't, either.
Is this something you'll have to expect when using Access 2000? Until now I was under the impression that, yes, it's not the greatest or fastest of databases, but it's easily setup and works well with databases up to about 100MB. The mdb files we're talking about are both about 8MB, with approx. 15 tables, so I didn't expect there to be a problem.
Thankful for any feedback,
mav
|
|
|
|
|
can someone plz give me a tutorial on how to accessing Paradox useing ADO.
i'd like to do it completely in Win32/SDK also
Thanks In Advance,
bin e
nothing
|
|
|
|
|
Hi,
I am creating a simple SPROC in .NET (SQL Server 2005) and I want to send the content of a DataTable to the pipe. I'm not sure how to do this (all this is still quite new to all of us!).
Pipe.Send takes a SqlDataReader or SqlDataRecord as an argument. However, the DataTable can only return a DataTableReader with CreateDataReader.
Is there a way to cast something into something else to make this work? Casting the DataTableReader to a SqlDataReader doesn't work.
Thanks!
|
|
|
|
|
i want to generate a trigger on a table whenever the date of the system changes. Can any one help me.
Programmers Solutions is QAU Programmers
http://www.qauprogrammers.com
http://www.linksexchangedirectory.org
http://www.appleiphonetricks.com
http://www.dubaieclassifieds.com
http://www.eshopsdubai.com
http://www.faheemhabib.com
modified on Thursday, February 3, 2011 7:41 AM
|
|
|
|
|
You need to create a sql server agent job an run it daily. You can't run a trigger on on insert update and delete.
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
i have problem to make connection to Server of Analysis Service in other computer. It's easy if i use local server, but i need to connect other server.
Can somebody help me?
NB: error message that show is "the server is either not started or too busy"
|
|
|
|
|
 Hello
I have empt table where job_id is field in it.
I want to update empt table if job_id = '1' or '3' by '999'
And if job_id not '1' or '3' then update by '456'
So what's wrong with my cursor
T.I.A
Shashank
-----------------------------------------------------
CREATE procedure lnt.textt
@qurstr varchar(10)
AS
Declare @in AS Varchar(10)
Declare crs_cc cursor for select job_id from empt FOR UPDATE OF job_id
open crs_cc
fetch next from crs_cc INTO @in
while @@fetch_status = 0
IF @qurstr = '1' OR @qurstr = '3'
BEGIN
UPDATE empt set job_id = '999'
where job_id = @qurstr
print 'U Miss By Miles!!!!'
print @qurstr
END
IF @qurstr IN (SELECT job_id from empt where job_id not in ('1','3'))
BEGIN
update empt set job_id = '456'
where job_id = @qurstr
print ' Success'
END
FETCH NEXT FROM crs_cc
CLOSE crs_cc
DEALLOCATE crs_cc
GO
|
|
|
|
|
|
After executing a select query the data provider returns the value in a varchar field in a .NET Unicode string, how does it determine the encoding conversion to perform? My guess is that it's determined by the collation setting on the column, but I'm not sure? For example, if the collation is SQL_Latin1_General_CP1_CI_AS, the Provider converts from code page 1252 to Unicode.
Does anyone know for sure the strategy applied here?
|
|
|
|
|
Hi
I have a table T with columns ID(int) , visible(bit);
visible cells are 1 or 0;
when i write "SELECT visible FROM T " in the query analyzer i got list of 1 and 0;
when i write "SELECT visible FROM T " in the VS in a sp and run it i got a list of 0 and -1 instead 1
WHY ?
|
|
|
|
|
|
Can someone please help me with error handling in SQL 2000? If an entry allready exist then the user must not be able to add the duplicate and he must be warned that there is a duplicate.
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
|
I have configured the table with the unique constraints but now I want to catch the error generated by SQL and then display or give it a meaningfull way to handle.
In other words, when the error occurs in SQL, I need to redirect the user to another page.
Illegal Operation
Making Computer Software Talk
|
|
|
|
|
Declare @intErrorCode int<br />
select @intErrorCode = @@Error<br />
<br />
begin transaction<br />
<br />
If @intErrorCode = 0<br />
begin<br />
-- insert SQL Statement<br />
set @intErrorCode = @@Error<br />
end<br />
<br />
If @intErrorCode = 0<br />
begin<br />
-- insert another SQL Statement<br />
set @intErrorCode = @@Error<br />
end<br />
<br />
<br />
IF @intErrorCode = 0<br />
commit transaction<br />
else<br />
rollback transaction<br />
return @intErrorCode
To handel the error in your page use the
Try <br />
Cmd.ExecuteNonQuery<br />
catch ex as sqlclient.sqlexception<br />
'Put code to redirect<br />
End Try
"People who never make mistakes, never do anything."
My blog
http://toddsnotsoamazinglife.blogspot.com/
|
|
|
|
|
Hi,
In a relational database, I would to like to know the performance difference between of using a string and an integer as the primary key field for the tabl;e. Will a column field of integer be indexed faster than a string column field ?
Thanks
|
|
|
|
|
CraigBurton wrote:
Will a column field of integer be indexed faster than a string column field ?
Yes, simply because the key will be smaller. The smaller the key the faster it can be indexed and retrieved because the index will be smaller overall and it won't have to pull so much data off the disk in order to scan the index.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Thanks
Besides integer is smaller than string, will implicitly
the indexing of integer is faster than string, if they are of
similar length ?
How about for the retrievel after indexing, should be same ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
