[SOLVED] Mysql query modification for best Performance

Issue

I am using a Round() function in MySQL query to get a SUM() value with 3 precision. But I am not sure whether this is the best way or get an actual value and round it off in java or js.
I am a bit confused about which is the best practice and why?

Select ROUND(SUM(ORDER_VALUE),3) from Orders

or

   Select SUM(ORDER_VALUE) from Orders  group by STOCK_ID:

after that 

   DecimalFormat formatter = new DecimalFormat("0.000");
   System.out.println(formatter.format(25.0)); 

Solution

You want to know whether it’s better to do your rounding to three decimal digits inside MySQL or in your application program.

As long as the SQL statement contains SUM(ORDER_VALUE), It Doesn’t Matter, at least as far as the accuracy goes. Before rounding, the SUM() operation uses the precision of the column it operates on.

And, the performance difference between your choices is far too small to measure. Far less than a microsecond.

Therefore, it’s a matter of making your code as clear, and as easy to reason about, as it possibly can be. You’re dealing with other peoples’ money, and you want to be able to convince yourself you’ve done it right.

When a future customer asks what happened to the fourth and fifth digits of precision, you want to give a short and correct answer.

Is it easier for you, and your future self a year from now, and your co-workers, to read the ROUND in SQL or the string-formatting stuff in Java?

I favor putting the business rules in one place in the code. The SQL SUM is a business rule, so the ROUND should go with it.

Answered By – O. Jones

Answer Checked By – Cary Denson (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *