RE: N+1 select problem for Map no matter what I try

[Henno Vermeulen]

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:curti...@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").
>
>