Unnecessary table join from EJB-QL

[nicknewman]

This is not a bug - more of an efficiency question.

I have two entities, say Book and Author, which have a 1-many relationship (one author writes many books).

I want to find the books written by an author, given the author's primary key.

For the EQB-QL I can write:

SELECT OBJECT(b)
FROM Book b
WHERE Book.author.pkey = ?1

This works fine, but the generated SQL is something like:

SELECT Book.pkey
FROM Book, Author
WHERE Book.author = Author.pkey
AND Author.pkey = ?1

It seems that the join is unnecessary and inefficient. If writing the SQL directly we would just have:

SELECT Book.pkey
FROM Book
WHERE Book.author = ?1

Is there any EQB-QL way to "encourage" JBoss to use the more efficient SQL? Or is this just a function of how smart the EGB-QL to SQL conversion is?

Thanks,
Nick

[juhalindfors]

Have you declared load-groups and set eager-loading and lazy loading groups for your columns? Have a look at the CMP doc for details.

[nicknewman]

No, I haven't done that. I wouldn't expect it to change the table-join that JBoss is generating, but it's certainly worth checking.

Thanks

[nicknewman]

In case anyone else is interested I'll post the answer which I eventually came up with myself.

Instead of using a finder on Book looking something like this:

bookHome.findByAuthorPk( authorPk );

use a finder on the Author and then ask for his books, something like this:

authorHome.findByPrimaryKey( authorPk ).getBooks();

or, possibly better yet (because it is more flexible) change the book finder to take an Author rather than a primary key, like this:

Author author =
authorHome.findByPrimaryKey( authorPk );
bookHome.findByAuthor( author );

Both of these solutions perform an Author lookup which might be considered unnecessary, but this is better than a table join.

Nick