Hibernate Select Queries Optimization

Question

I'm searching for the best way to optimize Hibernate select queries.

Here is a basic example:

BDD model 1 Client -> n Contracts -> n Options

The simplest manner to request all data of the client "xxxx" is something like:

final Query hqlQuery = jdbcTemplate.createHQLQuery("from Client cli left join fetch cli.contracts con left join fetch con.options where cli.id=:idClient");
hqlQuery .setString("idClient", "xxxx");

Client client = (Client) hqlQuery.uniqueResult();

Sometimes this is just not possible since there are two much data to return.

So, I split the request, somethink like:

// Query 1
final Query hqlQueryClient = jdbcTemplate.createHQLQuery("from Client cli left join fetch cli.contracts where cli.id=:clientId");
hqlQueryClient.setString("clientId", "xxxx");

Client client = (Client) hqlQueryClient.uniqueResult();

List<String> listContractIds = new ArrayList<String>();

for (Contract contract : client.getContracts()) {
  listContractIds.add(contract.getId());
}

// Query 2
final Query hqlQueryOptions = jdbcTemplate.createHQLQuery("from Option opt where opt.contract.id in(:contractIds)");
hqlQueryOptions.setParameterList("contractIds", listContractIds);

List<Option> options = hqlQueryClient.list();

But, with the second manner, I can't "inject" options in client object, so I have to deal with client and options objects in my code, and search in options list those which correspond to the contract I'm working with.

Is there a way to complete Hibernate object (client in my example) with values requested in a second time ?

Thanks for your help.

PS: ask if it's not clear, I'm French guy :)

lance-java · Answer 1 · 2015-10-23T14:11:25.707

0

<rant> I generally hate hibernate because it's such a time waster and also seems to run hundreds of queries when you'd only run a handful if writing the SQL manually <\rant>

If forced to use hibernate, I'd probably use 3 queries similar to

from Options as o join fetch o.contract as co join fetch co.client as cl where cl = :client
from Contracts as co join fetch co.client as cl where cl = :client
from Client where clientId = :clientId

Then I'd put them all into appropriate Map<Long, List> maps and do the joins in java.

edited Oct 23 '15 at 14:11

answered Oct 23 '15 at 14:05

lance-java

25,497
4
59
101

No way to inject the result of the 3 queries in the main object directly (instead of dealing with Map) ? – pierrefevrier Oct 23 '15 at 14:40
You might be ok with the first query, provided you only navigate from Option -> Contract -> Client. If you go the other way (Client -> Contract -> Option) you'll hit [n+1 selects](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue) issues – lance-java Oct 23 '15 at 19:26
You are right, but I can't navigate from Option -> Contract -> Client in my code, I'm doing the other way (Client -> Contract -> Option). To avoid n+1 selects problem, I never call `contrat.getOptions()`. instead, I filter results of Options query programmatically (via contrat id), but it's not as clean as just call `contrat.getOptions()`, that's the subject of this post: how can I do better :) – pierrefevrier Oct 23 '15 at 21:15
And i answered the subject of the post: 3 queries + maps :) – lance-java Oct 24 '15 at 22:32
Not exactly. The question was: is it possible to request the way you propose and then inject the result on the top level object (Client in example) to be able to iterate intuitively on Client children ? So I gess your response is no, that's not possible :( – pierrefevrier Oct 26 '15 at 08:49
You might be able to push the objects into the collections of the root object. But be careful because if you specify `cascade=CascadeType.ALL` you might find hibernate detecting changes to the collections and running lots of queries to see if it's actually an update. Also, invoking the collection getter might fire a query to get the child objects. As I said, hibernate is a time waster. The quicker you get rid of it, the happier you will be :) – lance-java Oct 26 '15 at 09:29
Ok, do I need to do this operation manually, Hibernate is not clever enough to do injection by itself by detecting they are in relation ? – pierrefevrier Oct 27 '15 at 10:46

score 0 · Answer 2 · answered Oct 23 '15 at 14:35

0

First: how much data do You have in result that the first query doesn't work? If you the result has so many rows and You want to optimise this query check if all data You get from db You realy need. Maybe You should make a projection to other object which is more flattened.

If you not process the data in Java and only passing it to the front-end consider pageing the result.

answered Oct 23 '15 at 14:35

Damian Orzepowski

66
4

Potentially 1 Client -> 50 Contracts -> 50 Options Let's say each client, each contract and each option has 20 columns, it means Hibernate has to deal with 50*20*50*20 = 1 000 000 columns. – pierrefevrier Oct 23 '15 at 14:57
And do You really need all those columns? – Damian Orzepowski Oct 23 '15 at 15:09
Not all but at least 50%. – pierrefevrier Oct 23 '15 at 15:11
So first of all you should take only columns You need. Second is what do You do with data - do You process it in Java? – Damian Orzepowski Oct 23 '15 at 15:14
You are right about columns but how can I select these I want from the 3 levels of objects (client, contract, option) AND have the result mapped on my client object ? Concerning processing, yes, I handle theses results in Java. – pierrefevrier Oct 23 '15 at 15:20
I don't know hql very well probably You need to add select clause. I know You can do this with Projections in criteria API. – Damian Orzepowski Oct 23 '15 at 17:09
About processing could You specify what kind of processing You do. If this is some kind of aggregation You should consider make this on db (so include this in the query) – Damian Orzepowski Oct 23 '15 at 17:12

score -2 · Answer 3 · answered Oct 23 '15 at 14:24

The benefit in using Hibernate is ORM. You could set up your classes as Entities. So you don't need to worry about simple queries anymore. Simply use JPA for that task. The entities could look like that:

@Entity
public class Client implements Serializable {
    private Long id;
    private ArrayList<Contract> contracts;
    // more attributes here
    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }

    @OneToMany
    public ArrayList<Contract> getContracts() {
        return contracts;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setContracts(ArrayList<Contract> contracts) {
        this.contracts = contracts;
    }
}

@Entity
public class Contract implements Serializable {
    private Long id;
    private List<Option> options;
    // more attributes here
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }

    @OneToMany
    public List<Option> getOptions() {
        return options;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setOptions(List<Option> options) {
        this.options = options;
    }
}

and so on...

Hi, your response is out of context, my question is not about mapping but how to query efficiently — pierrefevrier, Oct 23 '15 at 14:38
From the POV that you want to force to write SQLQs with Hibernate. It is totally out of context as you do not even understand what Hibernate was made for. So have fun — BuckRogers, Oct 23 '15 at 15:13

Hibernate Select Queries Optimization

3 Answers3