I'm using OpenJPA 2.1.1 on Karaf 2.2.4. I also use the aries transaction
support (0.3). I use SQL Server 2005.
I'm getting very slow query responses. Retrieving 330 rows with 5 columns
takes almost 4 seconds (about 100 bytes per row). Running the SQL Server
profiler I can see that the actual database time is much less (around 20-30
ms). It seems like the time is spent in Java. I'm not 100% sure of this but
this is my impression.
If I run the query direclty from within SQL Server Management studio, the
query is very quick (much less than one second).
Here is an excerpt from my code:
* public List<Customer> findCustomers(CustomerSearchCriteria theCriteria) {
*
* long t0 = System.nanoTime();*
* CriteriaBuilder builder = mEntityManager.getCriteriaBuilder();*
* long t1 = System.nanoTime();*
* CriteriaQuery<Customer> query = builder.createQuery(Customer.class);*
* long t2 = System.nanoTime();*
* Root<Customer> customer = query.from(Customer.class);*
* long t3 = System.nanoTime();*
* query.where(createCriteria(builder, customer, theCriteria));*
* long t4 = System.nanoTime();*
* query.orderBy(builder.asc(customer.get(Customer_.shortName)));*
* long t5 = System.nanoTime();*
*
*
* /**
* * Fetch the entries*
* */*
* TypedQuery<Customer> typedQuery = mEntityManager.createQuery(query);*
* long t6 = System.nanoTime();*
* List<Customer> result = typedQuery.getResultList(); *
* long t7 = System.nanoTime();*
* *
* System.out.println("t1: " + (t1-t0) / 1000000 + " ms");*
* System.out.println("t2: " + (t2-t1) / 1000000 + " ms");*
* System.out.println("t3: " + (t3-t2) / 1000000 + " ms");*
* System.out.println("t4: " + (t4-t3) / 1000000 + " ms");*
* System.out.println("t5: " + (t5-t4) / 1000000 + " ms");*
* System.out.println("t6: " + (t6-t5) / 1000000 + " ms");*
* System.out.println("t7: " + (t7-t6) / 1000000 + " ms");*
* *
* return result;*
* }*
As you can see, I did some measurements. All time is spent in between t6
and t7. I guess that is to be expected since that is where the query is
being executed.
My persistence.xml looks like this:
*<?xml version="1.0" encoding="UTF-8"?>*
*<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
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"*
* version="2.0">*
* <persistence-unit name="skistPU" transaction-type="JTA">*
*
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>*
* <jta-data-source>osgi:service/javax.sql.DataSource/(
osgi.jndi.service.name=jdbc/skistory)</jta-data-source>*
* <class>se.digia.skistory.domain.Customer</class>*
* <class>se.digia.skistory.domain.Statement</class>*
* <class>se.digia.skistory.domain.Transaction</class>*
* <exclude-unlisted-classes>true</exclude-unlisted-classes>*
* <validation-mode>NONE</validation-mode>*
* <properties>*
* <property name="openjpa.ConnectionFactoryMode" value="managed" />*
* <property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(ForeignKeys=true)" />*
* <property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(ForeignKeys=true)" />*
* <property name="openjpa.jdbc.DBDictionary"
value="org.apache.openjpa.jdbc.sql.SQLServerDictionary" />*
* <property name="openjpa.jdbc.UpdateManager" value="operation-order"
/>*
* <property name="openjpa.Log" value="DefaultLevel=INFO, Tool=INFO" />*
* </properties>*
* </persistence-unit>*
*</persistence>*
And I instantiate my datasource using Aries blueprint as follows:
*<?xml version="1.0" encoding="UTF-8"?>*
*<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"; xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"*
* xmlns:cm="http://aries.apache.org/blueprint/xmlns/blueprint-cm/v1.1.0";
xmlns:ext="http://aries.apache.org/blueprint/xmlns/blueprint-ext/v1.0.0"*
* xsi:schemaLocation="http://www.osgi.org/xmlns/blueprint/v1.0.0";
default-activation="lazy">*
*
*
* <cm:property-placeholder persistent-id="skist.datasource"
update-strategy="reload">*
* <cm:default-properties>*
* <cm:property name="user" value="user" />*
* <cm:property name="password" value="password" />*
* <cm:property name="host" value="localhost" />*
* <cm:property name="port" value="1433" />*
* <cm:property name="databaseName" value="skistory" />*
* </cm:default-properties>*
* </cm:property-placeholder>*
*
*
* <bean id="skistDataSource"
class="org.apache.commons.dbcp.BasicDataSource">*
* <property name="username" value="${user}" />*
* <property name="password" value="${password}" />*
* <property name="url"
value="jdbc:sqlserver://${host}:${port};databaseName=${databaseName};SendStringParametersAsUnicode=false"
/>*
* <property name="driverClassName"
value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />*
* </bean>*
*
*
* <service ref="skistDataSource" interface="javax.sql.DataSource">*
* <service-properties>*
* <entry key="osgi.jndi.service.name" value="jdbc/skistory" />*
* </service-properties>*
* </service>*
*
*
*</blueprint>*
I first used version 1.2 of Microsoft's JDBC driver but I have now upgraded
to version 3.0. It doesn't make any difference - actually the newer version
was a little slower.
I haven't seen this problem on MySql although I haven't measured it. Does
anyone know how to configure OpenJPA in order to get decent performance
with SQL Server 2005?
/Bengt
