SpringData and Querydsl generate not optimal query

Question

I am using JPA (Hibrenate v4.1.7), Spring Data(v1.4.1) and Query DSL(v3.2.3) for constructing my DAO layer on Postgres database.

But generated SQL for queries is not optimal for filtering on @ElementCollection relationship (similar problem exists with @OneToMany relationship)

I have entity:

@Entity
@Table(name = "doc_documents")
public class Document {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ElementCollection(fetch = FetchType.LAZY)
    @CollectionTable(name = "doc_document_titles",
        joinColumns =
        @JoinColumn(name = "document_id"))
    @Column(name = "title")
    private List<String> titles = new ArrayList<>();
….
)

And repository interface:

public interface DocumentRepo
        extends JpaRepository <Document, Long>, 
                QueryDslPredicateExecutor<Document> {
}

When I am executing my query:

Predicate crit = qDoc.titles.any().eq("x");
docRepo.findAll(crit);

This SQL is executed:

select
    document0_.id as id10_
from
    doc_documents document0_ 
where
    exists (
        select
            1 
        from
            doc_documents document1_ 
        inner join
            doc_titles title2_ 
                on document1_.id= title2_.document_id 
        where
            document1_.id=document0_.id 
            and locthesaur2_.title = ?
    )

But I think optimal SQL for this query should be without additional inner join:

select
    document0_.id as id10_
from
    doc_documents document0_ 
where
    exists (
        select
            1 
        from
            doc_titles title2_ 
        where
            title2_.document_id=document0_.id 
            and title2.title= ?
    )

Additional inner join seriously spoils performance. Is it possible to indicate QueryDSL to generate different query, or maybe I am just doing something wrong?

Performance issue is vital form my application, so without this optimization I can’t use QueryDSL for querying database (I have much more complex domain than I showed here).

Which Querydsl version are you using?

Timo Westkämper
– Timo Westkämper

2013-12-03 14:18:47 +00:00
Commented Dec 3, 2013 at 14:18 — Timo Westkämper
– Timo Westkämper, Commented Dec 3, 2013 at 14:18
Version of QueryDsl is 3.2.3

Palladium
– Palladium

2013-12-03 16:44:45 +00:00
Commented Dec 3, 2013 at 16:44 — Palladium
– Palladium, Commented Dec 3, 2013 at 16:44

Timo Westkämper · Accepted Answer · 2013-12-03 17:13:56Z

1

The predicate is internally translated into something like this on the JPQL level

exists (select 1 
        from Document doc2 
        inner join doc2.titles as doc2_titles
        where doc2 = doc and doc2_titles = ?)

It is not possible to use titles directly in the from part, since it not an entity. Let me know if you figure out a better way to express this in JPQL.

If there is a similar problem for OneToMany then please create a ticket for that.

answered Dec 3, 2013 at 17:13

Timo Westkämper

22.3k5 gold badges83 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SpringData and Querydsl generate not optimal query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related