Is there any difference in speed when ordering int vs. float?

Question

When retrieving records in a database, is there a difference between storing values ​​as float or decimal versus int when using ORDERBY in a SELECT statement?

Answer 1

It depends. You did not specify an RDBMS, so I can only speak with SQL Server, but data types have different storage costs associated with them. Ints range from 1 to 8 bytes, Decimal values are 5-17 and floats are from 4 to 8 bytes.

An RDBMS will need to read data pages from disk to find your data (in the worst case), and they can put as many lines on an 8-kilobyte data page. So, if you have ten-digit numbers of 17 bytes each, you will get the 1 / 17th number of lines read from the disk to read than you would if you would correctly evaluate your data and use tinyint with 1 byte cost to store X .

This storage cost will have a cascading effect when you sort (organize) your data. It will try to sort by memory, but if you have a bazillion lines and are starving for memory, it can reset the temporary storage for sorting, and you pay this cost again and again.

Indexes can help, because the data can be stored in a sorted way, but then again, if retrieving this data in memory may not be as efficient for obese data types.

[edit]

@Bohemian makes an accurate assessment of the performance of CPUs that are integer and floating point comparable, but this is surprisingly rare for the processor to be used on the database server. Most likely, you are limited by the IO disk subsystem and memory, so my answer focuses on the difference in speed between getting this data to the engine to perform the sort operation and the cost of comparing the CPU.

Answer 2

(Edited) Since int and float occupy exactly the same disk space and, of course, in memory - that is, 32 bits - the only differences in the way they are processed.

int should be faster than sorting float , because comparison is simpler: processors can compare ints in one machine cycle, but the float bit must be “interpreted” to get the value before comparison (not sure how many cycles, but probably more than one, although some processors may have special support for floating point comparisons).

Answer 3

In general, the choice of data types should be determined by whether the data type is suitable for storing the values ​​that need to be stored. If this type of data is inadequate, it does not matter how effective it is.

In terms of an i / o disk, the speed difference is second order. Don't worry about second-order effects until your design is good for first-order effects.

The correct design of the index will lead to a significant reduction in delays when the request can be received in sorted order to start with. However, this query is accelerated by slowing down other processes, such as processes that modify indexed data. A compromise must be considered to see if it is worth it.

In short, worry about what will double your I / O drive or worse before you worry about adding 10% to your i / o drive