You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Henno Vermeulen <he...@huizemolenaar.nl> on 2011/02/16 15:36:56 UTC
N+1 select problem for Map no matter what I try
When I have a Map inside a TestEntity (mapped either with @OneToMany or @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do the simple query "SELECT z FROM TestEntity z".
A solution to this would of course be great, but I would also be very happy if an OpenJPA developer could quickly try this out and confirm to me whether or not this is indeed an issue with OpenJPA or that it is simply expected behavior.
Regards,
Henno Vermeulen
(p.s. I have tried many settings and the problem occurs no matter what I try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my unanswered post "preventing N+1 select performance problem on maps").
RE: N+1 select problem for Map no matter what I try
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
Alright, thank you for looking into it. I will use a List of Entities that contain the key and value and see if performance is better.
Henno
-----Oorspronkelijk bericht-----
Van: Rick Curtis [mailto:curtisr7@gmail.com]
Verzonden: donderdag 17 februari 2011 17:27
Aan: users@openjpa.apache.org
Onderwerp: Re: N+1 select problem for Map no matter what I try
Heno -
It looks like this is the expected behavior. Keep an eye on OPENJPA-1920 for
any changes that we may try to make in this area.
Thanks,
Rick
On Thu, Feb 17, 2011 at 8:37 AM, Henno Vermeulen <he...@huizemolenaar.nl>wrote:
> That's great, thank you for your time!
>
> Heno
>
> -----Oorspronkelijk bericht-----
> Van: Rick Curtis [mailto:curtisr7@gmail.com]
> Verzonden: donderdag 17 februari 2011 15:36
> Aan: users@openjpa.apache.org
> Onderwerp: Re: N+1 select problem for Map no matter what I try
>
> Ok, now I was able to recreate the behavior that you reported... I'll dig
> into this a little more and get back with you.
>
> Thanks,
> Rick
>
> On Thu, Feb 17, 2011 at 4:25 AM, Henno Vermeulen <henno@huizemolenaar.nl
> >wrote:
>
> > Thank you for your reply! I tried and it unfortunately doesn't work. I
> > stripped my example down to the bare minimum. I am using OpenJPA 2.0.0
> > (downloaded openjpa-all using Maven).
> > In my real application I use compile time enhancement and mssql server, I
> > am running this test with hsqldb and openjpa as javaagent
> >
> (-javaagent:C:/Users/h.vermeulen/.m2/repository/org/apache/openjpa/openjpa-all/2.0.0/openjpa-all-2.0.0.jar).
> >
> > Here are my classes and settings:
> >
> > Persistence.xml:
> > <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> > xsi:schemaLocation="
> http://java.sun.com/xml/ns/persistencepersistence_1_0.xsd"
> > version="1.0">
> >
> > <persistence-unit name="testPU">
> >
> > <provider>org.apache.openjpa.persistence.PersistenceProviderImpl
> > </provider>
> > <class>entities.TestEntity</class>
> > <exclude-unlisted-classes>true</exclude-unlisted-classes>
> > <properties>
> > <property name="openjpa.jdbc.SynchronizeMappings"
> > value="buildSchema" />
> > <property name="openjpa.ConnectionDriverName"
> > value="org.hsqldb.jdbcDriver" />
> > <property name="openjpa.ConnectionURL"
> > value="jdbc:hsqldb:mem:test" />
> > <property name="openjpa.ConnectionUserName"
> > value="sa" />
> > <property name="openjpa.ConnectionPassword"
> value=""
> > />
> > <property name="openjpa.Log"
> > value="DefaultLevel=TRACE" />
> > </properties>
> > </persistence-unit>
> > </persistence>
> >
> > TestEntity.java:
> > package entities;
> >
> > import java.util.HashMap;
> > import java.util.Map;
> >
> > import javax.persistence.CascadeType;
> > import javax.persistence.Entity;
> > import javax.persistence.FetchType;
> > import javax.persistence.GeneratedValue;
> > import javax.persistence.Id;
> >
> > import org.apache.openjpa.persistence.PersistentMap;
> >
> > @Entity
> > public class TestEntity {
> >
> > @Id
> > @GeneratedValue
> > private Long id;
> >
> > @PersistentMap(elementCascade = CascadeType.PERSIST, fetch =
> > FetchType.EAGER)
> > private Map<String, String> strings = new HashMap<String,
> > String>();
> >
> > public TestEntity() {
> > }
> >
> > public Long getId() {
> > return id;
> > }
> >
> > public Map<String, String> getStrings() {
> > return strings;
> > }
> >
> > }
> >
> > MapPerformanceTest.java:
> > import java.util.List;
> >
> > import javax.persistence.EntityManagerFactory;
> > import javax.persistence.Persistence;
> >
> > import org.apache.openjpa.persistence.OpenJPAEntityManager;
> >
> > import entities.TestEntity;
> >
> > public class MapPerformanceTest {
> > private EntityManagerFactory factory;
> >
> > public void createEntityManagerFactory() {
> > factory = Persistence.createEntityManagerFactory("testPU",
> > System
> > .getProperties());
> > }
> >
> > private void persistInTransaction(TestEntity item) {
> > OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> > .createEntityManager();
> > em.getTransaction().begin();
> > em.persist(item);
> > em.getTransaction().commit();
> > em.close();
> > }
> >
> > private void createEntities() {
> > for (int i = 0; i < 100; i++) {
> > TestEntity entity = new TestEntity();
> > entity.getStrings().put("a", "test " + i);
> > entity.getStrings().put("b", "another test " + i);
> > persistInTransaction(entity);
> > }
> > }
> >
> > private List<TestEntity> findAllEntities() {
> > OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> > .createEntityManager();
> > em.getTransaction().begin();
> > String query = "SELECT z FROM " +
> > TestEntity.class.getSimpleName()
> > + " z";
> > List<TestEntity> result = em.createQuery(query,
> > TestEntity.class)
> > .getResultList();
> > em.getTransaction().commit();
> > em.close();
> > return result;
> > }
> >
> > public static void main(String[] args) {
> > MapPerformanceTest t = new MapPerformanceTest();
> > t.createEntityManagerFactory();
> > t.createEntities();
> > System.err.println("----------- find all -----------");
> > t.findAllEntities();
> > }
> > }
> >
> >
> > Henno Vermeulen
> >
> > -----Oorspronkelijk bericht-----
> > Van: Rick Curtis [mailto:curtisr7@gmail.com]
> > Verzonden: woensdag 16 februari 2011 16:49
> > Aan: users@openjpa.apache.org
> > Onderwerp: Re: N+1 select problem for Map no matter what I try
> >
> > Can you try using @PersistentMap(elementCascade =
> > CascadeType.PERSIST,fetch=FetchType.EAGER) rather than
> @ElementCollection?
> > I
> > don't see the N+1 select problem in my tests when using the persistentmap
> > annotation.
> >
> > Thanks,
> > Rick
> >
> > On Wed, Feb 16, 2011 at 8:36 AM, Henno Vermeulen <henno@huizemolenaar.nl
> > >wrote:
> >
> > > When I have a Map inside a TestEntity (mapped either with @OneToMany or
> > > @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do
> > the
> > > simple query "SELECT z FROM TestEntity z".
> > >
> > > A solution to this would of course be great, but I would also be very
> > happy
> > > if an OpenJPA developer could quickly try this out and confirm to me
> > whether
> > > or not this is indeed an issue with OpenJPA or that it is simply
> expected
> > > behavior.
> > >
> > > Regards,
> > > Henno Vermeulen
> > >
> > > (p.s. I have tried many settings and the problem occurs no matter what
> I
> > > try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my
> > > unanswered post "preventing N+1 select performance problem on maps").
> > >
> > >
> >
>
Re: N+1 select problem for Map no matter what I try
Posted by Rick Curtis <cu...@gmail.com>.
Heno -
It looks like this is the expected behavior. Keep an eye on OPENJPA-1920 for
any changes that we may try to make in this area.
Thanks,
Rick
On Thu, Feb 17, 2011 at 8:37 AM, Henno Vermeulen <he...@huizemolenaar.nl>wrote:
> That's great, thank you for your time!
>
> Heno
>
> -----Oorspronkelijk bericht-----
> Van: Rick Curtis [mailto:curtisr7@gmail.com]
> Verzonden: donderdag 17 februari 2011 15:36
> Aan: users@openjpa.apache.org
> Onderwerp: Re: N+1 select problem for Map no matter what I try
>
> Ok, now I was able to recreate the behavior that you reported... I'll dig
> into this a little more and get back with you.
>
> Thanks,
> Rick
>
> On Thu, Feb 17, 2011 at 4:25 AM, Henno Vermeulen <henno@huizemolenaar.nl
> >wrote:
>
> > Thank you for your reply! I tried and it unfortunately doesn't work. I
> > stripped my example down to the bare minimum. I am using OpenJPA 2.0.0
> > (downloaded openjpa-all using Maven).
> > In my real application I use compile time enhancement and mssql server, I
> > am running this test with hsqldb and openjpa as javaagent
> >
> (-javaagent:C:/Users/h.vermeulen/.m2/repository/org/apache/openjpa/openjpa-all/2.0.0/openjpa-all-2.0.0.jar).
> >
> > Here are my classes and settings:
> >
> > Persistence.xml:
> > <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> > xsi:schemaLocation="
> http://java.sun.com/xml/ns/persistencepersistence_1_0.xsd"
> > version="1.0">
> >
> > <persistence-unit name="testPU">
> >
> > <provider>org.apache.openjpa.persistence.PersistenceProviderImpl
> > </provider>
> > <class>entities.TestEntity</class>
> > <exclude-unlisted-classes>true</exclude-unlisted-classes>
> > <properties>
> > <property name="openjpa.jdbc.SynchronizeMappings"
> > value="buildSchema" />
> > <property name="openjpa.ConnectionDriverName"
> > value="org.hsqldb.jdbcDriver" />
> > <property name="openjpa.ConnectionURL"
> > value="jdbc:hsqldb:mem:test" />
> > <property name="openjpa.ConnectionUserName"
> > value="sa" />
> > <property name="openjpa.ConnectionPassword"
> value=""
> > />
> > <property name="openjpa.Log"
> > value="DefaultLevel=TRACE" />
> > </properties>
> > </persistence-unit>
> > </persistence>
> >
> > TestEntity.java:
> > package entities;
> >
> > import java.util.HashMap;
> > import java.util.Map;
> >
> > import javax.persistence.CascadeType;
> > import javax.persistence.Entity;
> > import javax.persistence.FetchType;
> > import javax.persistence.GeneratedValue;
> > import javax.persistence.Id;
> >
> > import org.apache.openjpa.persistence.PersistentMap;
> >
> > @Entity
> > public class TestEntity {
> >
> > @Id
> > @GeneratedValue
> > private Long id;
> >
> > @PersistentMap(elementCascade = CascadeType.PERSIST, fetch =
> > FetchType.EAGER)
> > private Map<String, String> strings = new HashMap<String,
> > String>();
> >
> > public TestEntity() {
> > }
> >
> > public Long getId() {
> > return id;
> > }
> >
> > public Map<String, String> getStrings() {
> > return strings;
> > }
> >
> > }
> >
> > MapPerformanceTest.java:
> > import java.util.List;
> >
> > import javax.persistence.EntityManagerFactory;
> > import javax.persistence.Persistence;
> >
> > import org.apache.openjpa.persistence.OpenJPAEntityManager;
> >
> > import entities.TestEntity;
> >
> > public class MapPerformanceTest {
> > private EntityManagerFactory factory;
> >
> > public void createEntityManagerFactory() {
> > factory = Persistence.createEntityManagerFactory("testPU",
> > System
> > .getProperties());
> > }
> >
> > private void persistInTransaction(TestEntity item) {
> > OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> > .createEntityManager();
> > em.getTransaction().begin();
> > em.persist(item);
> > em.getTransaction().commit();
> > em.close();
> > }
> >
> > private void createEntities() {
> > for (int i = 0; i < 100; i++) {
> > TestEntity entity = new TestEntity();
> > entity.getStrings().put("a", "test " + i);
> > entity.getStrings().put("b", "another test " + i);
> > persistInTransaction(entity);
> > }
> > }
> >
> > private List<TestEntity> findAllEntities() {
> > OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> > .createEntityManager();
> > em.getTransaction().begin();
> > String query = "SELECT z FROM " +
> > TestEntity.class.getSimpleName()
> > + " z";
> > List<TestEntity> result = em.createQuery(query,
> > TestEntity.class)
> > .getResultList();
> > em.getTransaction().commit();
> > em.close();
> > return result;
> > }
> >
> > public static void main(String[] args) {
> > MapPerformanceTest t = new MapPerformanceTest();
> > t.createEntityManagerFactory();
> > t.createEntities();
> > System.err.println("----------- find all -----------");
> > t.findAllEntities();
> > }
> > }
> >
> >
> > Henno Vermeulen
> >
> > -----Oorspronkelijk bericht-----
> > Van: Rick Curtis [mailto:curtisr7@gmail.com]
> > Verzonden: woensdag 16 februari 2011 16:49
> > Aan: users@openjpa.apache.org
> > Onderwerp: Re: N+1 select problem for Map no matter what I try
> >
> > Can you try using @PersistentMap(elementCascade =
> > CascadeType.PERSIST,fetch=FetchType.EAGER) rather than
> @ElementCollection?
> > I
> > don't see the N+1 select problem in my tests when using the persistentmap
> > annotation.
> >
> > Thanks,
> > Rick
> >
> > On Wed, Feb 16, 2011 at 8:36 AM, Henno Vermeulen <henno@huizemolenaar.nl
> > >wrote:
> >
> > > When I have a Map inside a TestEntity (mapped either with @OneToMany or
> > > @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do
> > the
> > > simple query "SELECT z FROM TestEntity z".
> > >
> > > A solution to this would of course be great, but I would also be very
> > happy
> > > if an OpenJPA developer could quickly try this out and confirm to me
> > whether
> > > or not this is indeed an issue with OpenJPA or that it is simply
> expected
> > > behavior.
> > >
> > > Regards,
> > > Henno Vermeulen
> > >
> > > (p.s. I have tried many settings and the problem occurs no matter what
> I
> > > try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my
> > > unanswered post "preventing N+1 select performance problem on maps").
> > >
> > >
> >
>
RE: N+1 select problem for Map no matter what I try
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
That's great, thank you for your time!
Heno
-----Oorspronkelijk bericht-----
Van: Rick Curtis [mailto:curtisr7@gmail.com]
Verzonden: donderdag 17 februari 2011 15:36
Aan: users@openjpa.apache.org
Onderwerp: Re: N+1 select problem for Map no matter what I try
Ok, now I was able to recreate the behavior that you reported... I'll dig
into this a little more and get back with you.
Thanks,
Rick
On Thu, Feb 17, 2011 at 4:25 AM, Henno Vermeulen <he...@huizemolenaar.nl>wrote:
> Thank you for your reply! I tried and it unfortunately doesn't work. I
> stripped my example down to the bare minimum. I am using OpenJPA 2.0.0
> (downloaded openjpa-all using Maven).
> In my real application I use compile time enhancement and mssql server, I
> am running this test with hsqldb and openjpa as javaagent
> (-javaagent:C:/Users/h.vermeulen/.m2/repository/org/apache/openjpa/openjpa-all/2.0.0/openjpa-all-2.0.0.jar).
>
> Here are my classes and settings:
>
> Persistence.xml:
> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="http://java.sun.com/xml/ns/persistencepersistence_1_0.xsd"
> version="1.0">
>
> <persistence-unit name="testPU">
>
> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl
> </provider>
> <class>entities.TestEntity</class>
> <exclude-unlisted-classes>true</exclude-unlisted-classes>
> <properties>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema" />
> <property name="openjpa.ConnectionDriverName"
> value="org.hsqldb.jdbcDriver" />
> <property name="openjpa.ConnectionURL"
> value="jdbc:hsqldb:mem:test" />
> <property name="openjpa.ConnectionUserName"
> value="sa" />
> <property name="openjpa.ConnectionPassword" value=""
> />
> <property name="openjpa.Log"
> value="DefaultLevel=TRACE" />
> </properties>
> </persistence-unit>
> </persistence>
>
> TestEntity.java:
> package entities;
>
> import java.util.HashMap;
> import java.util.Map;
>
> import javax.persistence.CascadeType;
> import javax.persistence.Entity;
> import javax.persistence.FetchType;
> import javax.persistence.GeneratedValue;
> import javax.persistence.Id;
>
> import org.apache.openjpa.persistence.PersistentMap;
>
> @Entity
> public class TestEntity {
>
> @Id
> @GeneratedValue
> private Long id;
>
> @PersistentMap(elementCascade = CascadeType.PERSIST, fetch =
> FetchType.EAGER)
> private Map<String, String> strings = new HashMap<String,
> String>();
>
> public TestEntity() {
> }
>
> public Long getId() {
> return id;
> }
>
> public Map<String, String> getStrings() {
> return strings;
> }
>
> }
>
> MapPerformanceTest.java:
> import java.util.List;
>
> import javax.persistence.EntityManagerFactory;
> import javax.persistence.Persistence;
>
> import org.apache.openjpa.persistence.OpenJPAEntityManager;
>
> import entities.TestEntity;
>
> public class MapPerformanceTest {
> private EntityManagerFactory factory;
>
> public void createEntityManagerFactory() {
> factory = Persistence.createEntityManagerFactory("testPU",
> System
> .getProperties());
> }
>
> private void persistInTransaction(TestEntity item) {
> OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> .createEntityManager();
> em.getTransaction().begin();
> em.persist(item);
> em.getTransaction().commit();
> em.close();
> }
>
> private void createEntities() {
> for (int i = 0; i < 100; i++) {
> TestEntity entity = new TestEntity();
> entity.getStrings().put("a", "test " + i);
> entity.getStrings().put("b", "another test " + i);
> persistInTransaction(entity);
> }
> }
>
> private List<TestEntity> findAllEntities() {
> OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> .createEntityManager();
> em.getTransaction().begin();
> String query = "SELECT z FROM " +
> TestEntity.class.getSimpleName()
> + " z";
> List<TestEntity> result = em.createQuery(query,
> TestEntity.class)
> .getResultList();
> em.getTransaction().commit();
> em.close();
> return result;
> }
>
> public static void main(String[] args) {
> MapPerformanceTest t = new MapPerformanceTest();
> t.createEntityManagerFactory();
> t.createEntities();
> System.err.println("----------- find all -----------");
> t.findAllEntities();
> }
> }
>
>
> Henno Vermeulen
>
> -----Oorspronkelijk bericht-----
> Van: Rick Curtis [mailto:curtisr7@gmail.com]
> Verzonden: woensdag 16 februari 2011 16:49
> Aan: users@openjpa.apache.org
> Onderwerp: Re: N+1 select problem for Map no matter what I try
>
> Can you try using @PersistentMap(elementCascade =
> CascadeType.PERSIST,fetch=FetchType.EAGER) rather than @ElementCollection?
> I
> don't see the N+1 select problem in my tests when using the persistentmap
> annotation.
>
> Thanks,
> Rick
>
> On Wed, Feb 16, 2011 at 8:36 AM, Henno Vermeulen <henno@huizemolenaar.nl
> >wrote:
>
> > When I have a Map inside a TestEntity (mapped either with @OneToMany or
> > @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do
> the
> > simple query "SELECT z FROM TestEntity z".
> >
> > A solution to this would of course be great, but I would also be very
> happy
> > if an OpenJPA developer could quickly try this out and confirm to me
> whether
> > or not this is indeed an issue with OpenJPA or that it is simply expected
> > behavior.
> >
> > Regards,
> > Henno Vermeulen
> >
> > (p.s. I have tried many settings and the problem occurs no matter what I
> > try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my
> > unanswered post "preventing N+1 select performance problem on maps").
> >
> >
>
Re: N+1 select problem for Map no matter what I try
Posted by Rick Curtis <cu...@gmail.com>.
Ok, now I was able to recreate the behavior that you reported... I'll dig
into this a little more and get back with you.
Thanks,
Rick
On Thu, Feb 17, 2011 at 4:25 AM, Henno Vermeulen <he...@huizemolenaar.nl>wrote:
> Thank you for your reply! I tried and it unfortunately doesn't work. I
> stripped my example down to the bare minimum. I am using OpenJPA 2.0.0
> (downloaded openjpa-all using Maven).
> In my real application I use compile time enhancement and mssql server, I
> am running this test with hsqldb and openjpa as javaagent
> (-javaagent:C:/Users/h.vermeulen/.m2/repository/org/apache/openjpa/openjpa-all/2.0.0/openjpa-all-2.0.0.jar).
>
> Here are my classes and settings:
>
> Persistence.xml:
> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="http://java.sun.com/xml/ns/persistencepersistence_1_0.xsd"
> version="1.0">
>
> <persistence-unit name="testPU">
>
> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl
> </provider>
> <class>entities.TestEntity</class>
> <exclude-unlisted-classes>true</exclude-unlisted-classes>
> <properties>
> <property name="openjpa.jdbc.SynchronizeMappings"
> value="buildSchema" />
> <property name="openjpa.ConnectionDriverName"
> value="org.hsqldb.jdbcDriver" />
> <property name="openjpa.ConnectionURL"
> value="jdbc:hsqldb:mem:test" />
> <property name="openjpa.ConnectionUserName"
> value="sa" />
> <property name="openjpa.ConnectionPassword" value=""
> />
> <property name="openjpa.Log"
> value="DefaultLevel=TRACE" />
> </properties>
> </persistence-unit>
> </persistence>
>
> TestEntity.java:
> package entities;
>
> import java.util.HashMap;
> import java.util.Map;
>
> import javax.persistence.CascadeType;
> import javax.persistence.Entity;
> import javax.persistence.FetchType;
> import javax.persistence.GeneratedValue;
> import javax.persistence.Id;
>
> import org.apache.openjpa.persistence.PersistentMap;
>
> @Entity
> public class TestEntity {
>
> @Id
> @GeneratedValue
> private Long id;
>
> @PersistentMap(elementCascade = CascadeType.PERSIST, fetch =
> FetchType.EAGER)
> private Map<String, String> strings = new HashMap<String,
> String>();
>
> public TestEntity() {
> }
>
> public Long getId() {
> return id;
> }
>
> public Map<String, String> getStrings() {
> return strings;
> }
>
> }
>
> MapPerformanceTest.java:
> import java.util.List;
>
> import javax.persistence.EntityManagerFactory;
> import javax.persistence.Persistence;
>
> import org.apache.openjpa.persistence.OpenJPAEntityManager;
>
> import entities.TestEntity;
>
> public class MapPerformanceTest {
> private EntityManagerFactory factory;
>
> public void createEntityManagerFactory() {
> factory = Persistence.createEntityManagerFactory("testPU",
> System
> .getProperties());
> }
>
> private void persistInTransaction(TestEntity item) {
> OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> .createEntityManager();
> em.getTransaction().begin();
> em.persist(item);
> em.getTransaction().commit();
> em.close();
> }
>
> private void createEntities() {
> for (int i = 0; i < 100; i++) {
> TestEntity entity = new TestEntity();
> entity.getStrings().put("a", "test " + i);
> entity.getStrings().put("b", "another test " + i);
> persistInTransaction(entity);
> }
> }
>
> private List<TestEntity> findAllEntities() {
> OpenJPAEntityManager em = (OpenJPAEntityManager) factory
> .createEntityManager();
> em.getTransaction().begin();
> String query = "SELECT z FROM " +
> TestEntity.class.getSimpleName()
> + " z";
> List<TestEntity> result = em.createQuery(query,
> TestEntity.class)
> .getResultList();
> em.getTransaction().commit();
> em.close();
> return result;
> }
>
> public static void main(String[] args) {
> MapPerformanceTest t = new MapPerformanceTest();
> t.createEntityManagerFactory();
> t.createEntities();
> System.err.println("----------- find all -----------");
> t.findAllEntities();
> }
> }
>
>
> Henno Vermeulen
>
> -----Oorspronkelijk bericht-----
> Van: Rick Curtis [mailto:curtisr7@gmail.com]
> Verzonden: woensdag 16 februari 2011 16:49
> Aan: users@openjpa.apache.org
> Onderwerp: Re: N+1 select problem for Map no matter what I try
>
> Can you try using @PersistentMap(elementCascade =
> CascadeType.PERSIST,fetch=FetchType.EAGER) rather than @ElementCollection?
> I
> don't see the N+1 select problem in my tests when using the persistentmap
> annotation.
>
> Thanks,
> Rick
>
> On Wed, Feb 16, 2011 at 8:36 AM, Henno Vermeulen <henno@huizemolenaar.nl
> >wrote:
>
> > When I have a Map inside a TestEntity (mapped either with @OneToMany or
> > @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do
> the
> > simple query "SELECT z FROM TestEntity z".
> >
> > A solution to this would of course be great, but I would also be very
> happy
> > if an OpenJPA developer could quickly try this out and confirm to me
> whether
> > or not this is indeed an issue with OpenJPA or that it is simply expected
> > behavior.
> >
> > Regards,
> > Henno Vermeulen
> >
> > (p.s. I have tried many settings and the problem occurs no matter what I
> > try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my
> > unanswered post "preventing N+1 select performance problem on maps").
> >
> >
>
RE: N+1 select problem for Map no matter what I try
Posted by Henno Vermeulen <he...@huizemolenaar.nl>.
Thank you for your reply! I tried and it unfortunately doesn't work. I stripped my example down to the bare minimum. I am using OpenJPA 2.0.0 (downloaded openjpa-all using Maven).
In my real application I use compile time enhancement and mssql server, I am running this test with hsqldb and openjpa as javaagent (-javaagent:C:/Users/h.vermeulen/.m2/repository/org/apache/openjpa/openjpa-all/2.0.0/openjpa-all-2.0.0.jar).
Here are my classes and settings:
Persistence.xml:
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd"
version="1.0">
<persistence-unit name="testPU">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl
</provider>
<class>entities.TestEntity</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />
<property name="openjpa.ConnectionDriverName" value="org.hsqldb.jdbcDriver" />
<property name="openjpa.ConnectionURL" value="jdbc:hsqldb:mem:test" />
<property name="openjpa.ConnectionUserName" value="sa" />
<property name="openjpa.ConnectionPassword" value="" />
<property name="openjpa.Log" value="DefaultLevel=TRACE" />
</properties>
</persistence-unit>
</persistence>
TestEntity.java:
package entities;
import java.util.HashMap;
import java.util.Map;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import org.apache.openjpa.persistence.PersistentMap;
@Entity
public class TestEntity {
@Id
@GeneratedValue
private Long id;
@PersistentMap(elementCascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
private Map<String, String> strings = new HashMap<String, String>();
public TestEntity() {
}
public Long getId() {
return id;
}
public Map<String, String> getStrings() {
return strings;
}
}
MapPerformanceTest.java:
import java.util.List;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import entities.TestEntity;
public class MapPerformanceTest {
private EntityManagerFactory factory;
public void createEntityManagerFactory() {
factory = Persistence.createEntityManagerFactory("testPU", System
.getProperties());
}
private void persistInTransaction(TestEntity item) {
OpenJPAEntityManager em = (OpenJPAEntityManager) factory
.createEntityManager();
em.getTransaction().begin();
em.persist(item);
em.getTransaction().commit();
em.close();
}
private void createEntities() {
for (int i = 0; i < 100; i++) {
TestEntity entity = new TestEntity();
entity.getStrings().put("a", "test " + i);
entity.getStrings().put("b", "another test " + i);
persistInTransaction(entity);
}
}
private List<TestEntity> findAllEntities() {
OpenJPAEntityManager em = (OpenJPAEntityManager) factory
.createEntityManager();
em.getTransaction().begin();
String query = "SELECT z FROM " + TestEntity.class.getSimpleName()
+ " z";
List<TestEntity> result = em.createQuery(query, TestEntity.class)
.getResultList();
em.getTransaction().commit();
em.close();
return result;
}
public static void main(String[] args) {
MapPerformanceTest t = new MapPerformanceTest();
t.createEntityManagerFactory();
t.createEntities();
System.err.println("----------- find all -----------");
t.findAllEntities();
}
}
Henno Vermeulen
-----Oorspronkelijk bericht-----
Van: Rick Curtis [mailto:curtisr7@gmail.com]
Verzonden: woensdag 16 februari 2011 16:49
Aan: users@openjpa.apache.org
Onderwerp: Re: N+1 select problem for Map no matter what I try
Can you try using @PersistentMap(elementCascade =
CascadeType.PERSIST,fetch=FetchType.EAGER) rather than @ElementCollection? I
don't see the N+1 select problem in my tests when using the persistentmap
annotation.
Thanks,
Rick
On Wed, Feb 16, 2011 at 8:36 AM, Henno Vermeulen <he...@huizemolenaar.nl>wrote:
> When I have a Map inside a TestEntity (mapped either with @OneToMany or
> @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do the
> simple query "SELECT z FROM TestEntity z".
>
> A solution to this would of course be great, but I would also be very happy
> if an OpenJPA developer could quickly try this out and confirm to me whether
> or not this is indeed an issue with OpenJPA or that it is simply expected
> behavior.
>
> Regards,
> Henno Vermeulen
>
> (p.s. I have tried many settings and the problem occurs no matter what I
> try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my
> unanswered post "preventing N+1 select performance problem on maps").
>
>
Re: N+1 select problem for Map no matter what I try
Posted by Rick Curtis <cu...@gmail.com>.
Can you try using @PersistentMap(elementCascade =
CascadeType.PERSIST,fetch=FetchType.EAGER) rather than @ElementCollection? I
don't see the N+1 select problem in my tests when using the persistentmap
annotation.
Thanks,
Rick
On Wed, Feb 16, 2011 at 8:36 AM, Henno Vermeulen <he...@huizemolenaar.nl>wrote:
> When I have a Map inside a TestEntity (mapped either with @OneToMany or
> @ElementCollection), OpenJPA 2 always performs N + 1 selects when I do the
> simple query "SELECT z FROM TestEntity z".
>
> A solution to this would of course be great, but I would also be very happy
> if an OpenJPA developer could quickly try this out and confirm to me whether
> or not this is indeed an issue with OpenJPA or that it is simply expected
> behavior.
>
> Regards,
> Henno Vermeulen
>
> (p.s. I have tried many settings and the problem occurs no matter what I
> try. See https://issues.apache.org/jira/browse/OPENJPA-1920 and my
> unanswered post "preventing N+1 select performance problem on maps").
>
>