3 Replies Latest reply on May 19, 2003 2:36 PM by Nick Newman

    Unnecessary table join from EJB-QL

    Nick Newman Newbie

      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