Path: csiph.com!eternal-september.org!feeder.eternal-september.org!aioe.org!.POSTED!not-for-mail
From: =?UTF-8?Q?Arne_Vajh=c3=b8j?= <arne@vajhoej.dk>
Newsgroups: comp.lang.java.programmer
Subject: Re: Performance JPA
Date: Wed, 11 Nov 2015 19:10:09 -0500
Organization: Aioe.org NNTP Server
Lines: 105
Message-ID: <n20ld4$3dd$1@speranza.aioe.org>
References: <87lhagkcsh.fsf@Equus.decebal.nl> <563763d9$0$23727$e4fe514c@news.xs4all.nl> <87d1vrk20x.fsf@Equus.decebal.nl> <n1a51v$4qh$1@dont-email.me> <87ziyvhs0r.fsf@Equus.decebal.nl> <n1b13k$rd7$1@dont-email.me> <87mvuui2ws.fsf@Equus.decebal.nl> <87si4e49ja.fsf@Equus.decebal.nl> <n1u3ne$hvk$1@speranza.aioe.org> <8737wc425d.fsf@Equus.decebal.nl>
NNTP-Posting-Host: 1lM8B9/qdtKBc/uIRK2Gjw.user.speranza.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Thunderbird/38.3.0
X-Notice: Filtered by postfilter v. 0.8.2
Xref: csiph.com comp.lang.java.programmer:33111

On 11/11/2015 5:11 PM, Cecil Westerhof wrote:
> On Wednesday 11 Nov 2015 01:56 CET, Arne Vajhøj wrote:
>> On 11/10/2015 2:07 AM, Cecil Westerhof wrote:
>>> But I am told that JPA does a lot of things beside retrieving
>>> things out of the database and that those other things JPA is doing
>>> are the problem. Sounds far-fetched to me, but this is the first
>>> time I am working with JPA. Could they be right? If so: how do we
>>> pinpoint this?
>>
>> JPA does not do things randomly. It does what you tell it to do.
>
> At last I have some useful metrics. It certainly looks like it that
> JPA is the problem: JPA pauses 4.5 seconds before it starts executing
> the datbase queries.
>
> We use the following code:
>      public T read(K id) {
>          System.out.println("######################################## read enter");
>          T entity = em.find(entityClass, id);
>          System.out.println("######################################## read leave");
>          return entity;
>      }
>
> and this results in:
>      [11-11-15 15:13:53:590 CET] 000000ad SystemOut O ######################################## read enter
>      [11-11-15 15:13:58:112 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn -1937712648> executing prepstmnt 212337536 SELECT …
>      [11-11-15 15:13:58:118 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn -1937712648> [6 ms] spent
>      [11-11-15 15:13:58:119 CET] 000000ad jdbc_JDBC 3 openjpa.jdbc.JDBC: Trace: <t 728215633, conn -1937712648> [0 ms] close
>      [11-11-15 15:13:58:120 CET] 000000ad SystemOut O ######################################## read leave
>
>
> Somewhere else we have:
>      System.out.println("######################################## 6");
>
>      List<BeconBericht> berichten = query.getResultList();
>      System.out.println("######################################## leave");
>
> and this results in:
>      [11-11-15 15:13:58:160 CET] 000000ad SystemOut O ######################################## 6
>      [11-11-15 15:13:58:161 CET] 000000ad Query 3 openjpa.Query: Trace: Executing query: [SELECT bb FROM …
>      [11-11-15 15:14:02:670 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn 1696992183> executing prepstmnt -729574466 SELECT …
>      [11-11-15 15:14:02:677 CET] 000000ad jdbc_SQL 3 openjpa.jdbc.SQL: Trace: <t 728215633, conn 1696992183> [6 ms] spent
>      [11-11-15 15:14:02:720 CET] 000000ad jdbc_JDBC 3 openjpa.jdbc.JDBC: Trace: <t 728215633, conn 1696992183> [8 ms] close
>      [11-11-15 15:14:02:720 CET] 000000ad SystemOut O ######################################## leave
>
> It probably has to do with the database that is used. When using Derby
> with Unit Tests we do not have this problem. The test where we have
> this problem uses a DB2 database running on a mainframe. When Those
> 4.5 seconds would disappear we would have a comparable performance
> with JPA as we have with JDBC I think.

I think you are on the right track now.

> It should not be a problem with the connection pool. We are using an
> IBM wasserver 8.0 and this should take care of the connection pool.
>
> The relevant parts of the persistence.xml:
>      <properties>
>      <property name="openjpa.Compatibility" value="QuotedNumbersInQueries=true" />
>      <property name="openjpa.ConnectionDriverName" value="com.ibm.db2.jcc.DB2Driver" />
>      <property name="openjpa.ConnectionFactoryProperties" value="PrintParameters=true" />
>      <property name="openjpa.jdbc.DBDictionary" value="org.apache.openjpa.jdbc.sql.DB2Dictionary" />
>      <property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE" />
>      <property name="openjpa.DataCache" value="true (EnableStatistics=false)"/>
>      <property name="openjpa.RuntimeUnenhancedClasses" value="unsupported" />
>      <property name="openjpa.RemoteCommitProvider" value="sjvm"/>
>      <property name="openjpa.ReadLockLevel" value = "none" />
>      </properties>
>
> Anyone an idea what is happening here and what to do about it?
>
> If any other information is needed: let me know.

That persistence.xml does not look correct.

<property name="openjpa.ConnectionDriverName" 
value="com.ibm.db2.jcc.DB2Driver" />

specifies a JDBC driver not DataSource.

That may (*) make JPA not use WAS connection pool but try to establish
a connection for every operation. And that is very bad.

*) I don't know OpenJPA well enough to say.

The technique may work fine for a local database with a simple login
protocol but with a remote database and complex protocol, then it could
create a huge overhead.

But we would need to see the rest of the persistence,xml to be sure
(the standard part actually determines whether it is driver or
data source).

Try see:

http://stackoverflow.com/questions/3526434/ibm-websphere-jpa-configuration-how-to-update-persistence-xml

for an example of a persistence.xml that looks way
more I would expect (no driver, data source, all managed).

Arne