What are the performance impact of using Round instead of Convert

SET @example=969.3333
SELECT ROUND(@example,1)
  • 4
    I think you should pick the one that gives you the datatype you want instead of focusing on performance. ROUND() will give you a 8 byte float value and CONVERT will give you a 5 byte DECIMAL(4,1) value. – Mikael Eriksson Jan 8 '16 at 9:22

I really wonder if you got a solid business case for worrying about performance issues in rounding. Unless you are doing thousands of conversions per second, this really is non-issue.

The standard approach is to measure the performance differences. First you got to make sure that your ROUND() and CONVERT() actually do the same thing. Are you sure about that? After you are comfortable with the idea that both algorithms return same result, run your test cases.

On my test system, looping the round/convert 10 million times each, measuring the duration in milliseconds and running the test set five times provides results as such:

convert     round       delta
----------- ----------- -----------
7883        7546        337
7770        7510        260
7733        7563        170
7750        7466        284
7780        7513        267

By this test, it looks like ROUND() performs slightly better on my system. Your mileage is quite likely to vary. For ten million iterations, ROUND() saves on the average 250 milliseconds. That is, ROUND() performs some 250 nanoseconds faster than CONVERT(). I highly doubt if this is relevant to most real-world issues - unless one is developing something like high-frequency trading algorithm or the like.

  • It is likely that the difference in times that you measured is due to operations with float type being generally faster than with decimal type, not due to round vs convert functions as such. But, I didn't do any performance tests of float myself to prove it. – Vladimir Baranov Jan 8 '16 at 12:19
  • @VladimirBaranov Fair enough, measurement must be done with proper care. Anyway, the difference observed is so small that unless there is really an issue, I wouldn't bother with further testing. – vonPryz Jan 8 '16 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.