CODEWITHSUNDEEP
sqlhibernatejpa
MavidDeyers
MavidDeyers

Reputation: 290

Hibernate - Query becomes slow when I want to skip condition

I have the following sample query that terminates in < 0.5 second and returns data via Hibernate:

Query qry = em.createQuery(
              "select e from Example e where e.ids in :ids ")
              .setParameter("ids", ids); // List<String> ids

Since the list can be null or empty, I included the following trivial condition check that terminates just as quickly on the database side:

Query qry = em.createQuery(
              "select e from Example e where (:idFilterUsed = false OR e.ids in :ids ) ")
              .setParameter("ids", ids) // List<String> 
              .setParameter("idFilterUsed", idFilterUsed); // boolean

Even if the lower query should seem to have only one small trivial condition more, the query and mapping takes between 12 and 20 seconds instead of < 0.5s !

I can not explain what I have already done and what has not helped me :

Anybody have an idea what it could be or are there alternatives to throw out such IN queries should a condition be present? Many of my queries need several such condition checks.

The queries run on an Oracle 12g Database and im using Hibernate 5.4.27.

edit: To avoid misunderstandings -> the query has this extreme performance loss IF the input list (ids) is filled with at least one ID and idFilterUser = true. It is logical that the query is slow when the list is empty, but practically this got no influence because there are more conditions attached to it.

Upvotes: 0

Views: 394

Answers (1)

Oleksii Valuiskyi
Oleksii Valuiskyi

Reputation: 2841

The clause :idFilterUsed = false has nothing to do with the database.

I suggest two appropriate options to skip condition:

1. For one optional parameter

if(idFilterUsed) {
   qry = em.createQuery(
                  "select e from Example e where e.ids in :ids")
                  .setParameter("ids", ids);
} else {
   qry = em.createQuery("select e from Example e");
}

2. For multiple optional parameters use Criteria API or Spring Specification.

public class ExampleSpecification {

    public static Specification<Example> idIn(List<Long> ids, boolean idFilterUsed) {
        return (root, query, builder) -> {
            if (!idFilterUsed) {
                return builder.conjunction();
            }
    
            return root.get("ids").in(ids);
        };
    }
}

public interface ExampleRepository
        extends JpaSpecificationExecutor<Example> {
}

List<Long> ids = Arrays.asList(1L, 2L, 5L);
boolean idFilterUsed = false;

exampleRepository.findAll(ExampleSpecification.idIn(ids, idFilterUsed));

Look an example here

Upvotes: 1

Related Questions