openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Catalina Wei (JIRA)" <j...@apache.org>
Subject [jira] Updated: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne relation
Date Thu, 16 Oct 2008 19:45:44 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Catalina Wei updated OPENJPA-744:
---------------------------------

    Fix Version/s:     (was: 1.2.1)
                   1.3.0

The fix for this performance improvement is checked in under svn  trunk r705335.
It has not been applied to any svn branches.

> Extra SQL on LAZY/EAGER  ManyToOne relation
> -------------------------------------------
>
>                 Key: OPENJPA-744
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-744
>             Project: OpenJPA
>          Issue Type: Sub-task
>          Components: jdbc
>    Affects Versions: 1.2.1, 1.3.0
>            Reporter: Catalina Wei
>            Assignee: Catalina Wei
>             Fix For: 1.3.0
>
>
> OPENJPA-241 has eliminated some extra SQLs for  2 entities  having a LAZY OneToMany,
 and inverse EAGER ManyToOne relations.
> There are still  extra SQLs that can be avoided in the cases where  2 entities are related
by  an EAGER OneToMany,  and inverse LAZY ManyToOne relations.
> Consider the following 2 entities:
>     Publisher  (1) <--->  (M)  Magazine
>     @Entity
> public class Publisher implements Serializable {
>     @Id
>     @GeneratedValue(strategy = GenerationType.IDENTITY)
>     @Column(name="id")
>     private int id;
>     @OneToMany(mappedBy="idPublisher",  fetch=FetchType.EAGER)
>     private Set<Magazine> magazineCollection;   
> ...
> }
> @Entity
> public class Magazine implements Serializable {
>     @Id
>     @GeneratedValue(strategy = GenerationType.IDENTITY)
>     @Column(name="id")
>     private int id;
>     @ManyToOne(fetch=FetchType.LAZY)
>     @JoinColumn(name="id_publisher")
>     private Publisher idPublisher;
> ...
> }
> Query = em.createQuery("SELECT p from Publisher p);
> Since Publisher has a OneToMany Eager relation (magazines), We generate following  SQLs

>  (1)  SELECT t0.id, t0.name FROM Publisher t0
>  (2)  SELECT t0.id, t1.id, t1.date_published, t1.id_publisher, t1.name FROM Publisher
t0 INNER JOIN Magazine t1 ON t0.id = t1.id_publisher ORDER BY t0.id ASC
> However, the following extra SQLs are also generated (making additional database trips)
>  (3) SELECT t0.name, t1.id_publisher, t1.id, t1.date_published, t1.name FROM Publisher
t0 LEFT OUTER JOIN Magazine t1 ON t0.id = t1.id_publisher WHERE t0.id = ? [params=(int) 2]
> ... more depending on how many publishers there are in the database.
> The fact that SQL(2) has  already returned all magazines of all publishers, SQL (3) is
unnecessary. 
> The inverse ManyToOne relation should  be established from the SQL (2) result.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message