CODEWITHSUNDEEP
hibernateperformance
Chillax
Chillax

Reputation: 4698

Hibernate Performance Tuning

I am having around 1000 different id's for which I need to run the below hibernate query as many times. Based on the records retrieved (the record need not be present) , I do a bit of processing before entering it in another table.

The issue here is the whole process of retrieving the records takes approx 20-25 sec. Is this normal? Can the query be tweeked further to reduce the time?

(The below hibernate query will be executed for each id)

String query = "select emp from Employee emp where emp.id = '"+id"' and emp.status.statusCd = 'A'";

Note: Employee is a table with around 30 columns. status_cd and id are VARCHAR.

Upvotes: 1

Views: 1620

Answers (1)

bpgergo
bpgergo

Reputation: 16037

First of all, never use string concatenation to set variable fields in SQL.

String queryString = "select emp from Employee emp where emp.id = :id and emp.status.statusCd = 'A'";
Query query = Session.createQuery(queryString);
query.setInteger("id", 168);

Second, you could try to get all the target records with one query, docs:

String queryString = "select emp from Employee emp where emp.id in (:id) and emp.status.statusCd = 'A'";
Query query = Session.createQuery(queryString);
Collection<Integer> idList = new ArrayList<Integer>();
query.setParameterList("id", idList);

Then you would iterate over the results of this query and process each record as you processed them previously.

Third, if you execute additional SQL while processing each individual record, then you should also collect them and execute them in a batch.

Upvotes: 4

Related Questions