Calculation on query vs programmatically

Question

i m working on Java EE projects using Hibernate as ORM , I have come to a phase where i have to perform some mathematical calculation on my Classes , like SUM , COUNT , addition and division .

i have 2 solutions :

To select my classes and apply those operation programmatically in my code
To do calculations on my named queries

i want to please in terms of performance and speed , which one is better ?

And thank you

I believe it is simple to test both and decide. Do you have some performance test data? — Juned Ahsan, Jul 13 '14 at 02:27
well my classes are so big and complicated , test the both ways wont be that easy ( need lot of time ) , so i prefered to ask here hoping to find an answer before i try to test it my self — elmetni hamza, Jul 13 '14 at 02:28
Checke this : http://stackoverflow.com/questions/23025030/hibernate-criteria-vs-named-query — Abhijeet Panwar, Jul 13 '14 at 02:30

score 2 · Accepted Answer · answered Jul 13 '14 at 04:03

If you are going to load the same entities that you want to do the aggregation on from the database in the same transaction, then the performance will be better if you do the calculation in Java. It saves you one round-trip to the database, because in that case you already have the entities in memory. Other benefits are:

Easier to unit-test the calculation because you can stick to a Java-based unit testing framework
Keeps the logic in one language
Will also work for collections of entities that haven't been persisted yet

But if you're not going to load the same set of entities that you want to do the calculation on, then you will get a performance improvement in almost any situation if you let the database do the calculation. The more entities are involved, the bigger the performance benefit.

Imagine doing a summation over all line items in this year's orders, perhaps several million of them.

It should be clear that having to load all these entities into the memory of the Java process across a TCP connection (even if it is within the same machine) first will take more time, and more memory, than letting the database perform the calculation.

And if your mapping requires additional queries per entity, then Hibernate would have at least one extra round-trip to the database for every entity, in which case the performance benefits of calculating things in SQL on the database would be even bigger.

Perfect answer Erwin , it s what i have been looking for , Thank you :) — elmetni hamza, Jul 13 '14 at 04:39

score 0 · Answer 2 · answered Jul 13 '14 at 02:42

0

Are these calculation on the entities (or data)? if yes, then you can indeed go for queries(or even faster, use sql queries iso hql). From performance perspective ,IMO, stored procedures shines but people don't use them so often with hibernate.

Also, if you have some frequent repetitive calculation, try using caching in your application.

answered Jul 13 '14 at 02:42

Puneetsri

234
2
7

ya those calculation on the entities , such as addition and multiplication of attributs – elmetni hamza Jul 13 '14 at 03:18

Calculation on query vs programmatically

2 Answers2