You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Catalina Wei (JIRA)" <ji...@apache.org> on 2008/10/13 06:34:44 UTC
[jira] Created: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
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
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.
[jira] Updated: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick updated OPENJPA-744:
---------------------------------
Fix Version/s: 1.2.1
> 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: Michael Dick
> Fix For: 1.2.1, 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.
[jira] Updated: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ 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.3.0)
1.2.1
Fix also checked into 1.2.x branch r704586.
> 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.2.1
>
>
> 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.
[jira] Resolved: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei resolved OPENJPA-744.
----------------------------------
Resolution: Fixed
Fix Version/s: 2.0.0
Assignee: Catalina Wei (was: Michael Dick)
Fix applied to 1.2.x under r713857.
> 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.2.1, 1.3.0, 2.0.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.
[jira] Updated: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Reopened: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael Dick reopened OPENJPA-744:
----------------------------------
Assignee: Michael Dick (was: Catalina Wei)
I think this issue will eventually be merged to 1.2.x, but it needs some additional testing. I'll merge the changes when that testing has completed.
> 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: Michael Dick
> 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.
[jira] Resolved: (OPENJPA-744) Extra SQL on LAZY/EAGER ManyToOne
relation
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-744?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei resolved OPENJPA-744.
----------------------------------
Resolution: Fixed
Fix Version/s: 1.3.0
fix checked in under trunk r703912
> 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.