0

I have a large SQL database running on SQL Server. I have to perform arithmetic operations on large volumes of data and I'm looking to improve performance. I currently store amounts using the FLOAT data type and I have been reading about numeric data types to see if another data type would improve speed.

I read this question that recommended using DECIMAL() when possible. I implemented DECIMAL(18, 6) as I need 6 digits after the decimal point. However I noticed a small decline on queries speed.

Is there a faster data type I can use instead of float? How can I improve arithmetic performance in my DB?

Community
  • 1
  • 1
lisovaccaro
  • 32,502
  • 98
  • 258
  • 410
  • I'm using SQL Server. – lisovaccaro Jan 13 '17 at 18:18
  • 1
    Float is probably not a good choice if you are concerned with accuracy because float is an approximate datatype and not all values will work exactly. Your decimal(18,6) is a pretty large decimal. Do you really need them that large? That allows for 12 significant digits. Also, a datatype is neither faster or slower than another. There is no speed associated with them. It is your query, the table structure and the indexing that makes the performance differences. Can you share your query, table structure and indexes? – Sean Lange Jan 13 '17 at 18:44
  • If you can use `decimal(9,6)` you will drop your storage size from 9 bytes to 5 bytes. – SqlZim Jan 13 '17 at 22:27

1 Answers1

0

If you can use a smaller data type (e.g. decimal(9,6)), you might see an improvement in speed over a large volume of data as you described.

Depending on the type of operations you are doing, the size of the data types for the results may increase.

If you are multiplying two decimal(18,6) (9 bytes) your resulting datatype would be decimal(37,16) (17 bytes).

If you are multiplying two decimal(9,6) (5 bytes) your resulting datatype would be decimal(19,12) (9 bytes).

See this article on MSDN for the full chart and explanation: Precision, Scale, and Length (Transact-SQL)

Operations and resulting precision and scale

SqlZim
  • 37,248
  • 6
  • 41
  • 59