Return-Path: 
 <users-return-9726-apmail-openjpa-users-archive=openjpa.apache.org@openjpa.apache.org>
X-Original-To: apmail-openjpa-users-archive@minotaur.apache.org
Delivered-To: apmail-openjpa-users-archive@minotaur.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 227C07B5F
	for <apmail-openjpa-users-archive@minotaur.apache.org>;
 Wed, 21 Dec 2011 16:18:30 +0000 (UTC)
Received: (qmail 75257 invoked by uid 500); 21 Dec 2011 16:18:29 -0000
Delivered-To: apmail-openjpa-users-archive@openjpa.apache.org
Received: (qmail 75214 invoked by uid 500); 21 Dec 2011 16:18:29 -0000
Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:users-help@openjpa.apache.org>
List-Unsubscribe: <mailto:users-unsubscribe@openjpa.apache.org>
List-Post: <mailto:users@openjpa.apache.org>
List-Id: <users.openjpa.apache.org>
Reply-To: users@openjpa.apache.org
Delivered-To: mailing list users@openjpa.apache.org
Received: (qmail 75206 invoked by uid 99); 21 Dec 2011 16:18:29 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Dec 2011 16:18:29 +0000
X-ASF-Spam-Status: No, hits=2.2 required=5.0
	tests=HTML_MESSAGE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of bengt.rodehav@gmail.com
 designates 209.85.214.174 as permitted sender)
Received: from [209.85.214.174] (HELO mail-tul01m020-f174.google.com)
 (209.85.214.174)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Dec 2011 16:18:24 +0000
Received: by obcwo16 with SMTP id wo16so4521732obc.33
        for <users@openjpa.apache.org>; Wed, 21 Dec 2011 08:18:03 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=gamma;
        h=mime-version:sender:date:x-google-sender-auth:message-id:subject
         :from:to:content-type;
        bh=i8fK5KBmA4MbDRx9XSU4ORYVkaV6sXl938zRhYX6m+M=;
        b=Y1d1ZejDKBuOBv7P7Tx5N10MvvnUaRmBAkiUeMFmHiAOy/W0ch4ozdZ3m5gLzlgaxm
         LggGITORHt4/fdEqqbvV8aHEm4ejeOA35NIX8xuLVisI0nwvFaK+I1vhNYhB42zYZhf4
         tMeEQNmDuCnixGuDEHnzM1Mhy7m7l219CFefk=
MIME-Version: 1.0
Received: by 10.182.172.9 with SMTP id ay9mr6348162obc.28.1324484283353; Wed,
 21 Dec 2011 08:18:03 -0800 (PST)
Sender: bengt.rodehav@gmail.com
Received: by 10.182.231.70 with HTTP; Wed, 21 Dec 2011 08:18:03 -0800 (PST)
Date: Wed, 21 Dec 2011 17:18:03 +0100
X-Google-Sender-Auth: Pl683rU1Mho23GepGfNMXR5lTFc
Message-ID: 
 <CAJ0TPGK6jU-a=wUFWv+raom4kihf+0re-V7aqtFy2+pbXYEt1w@mail.gmail.com>
Subject: Slow query with SQL Server
From: Bengt Rodehav <bengt@rodehav.com>
To: users@openjpa.apache.org
Content-Type: multipart/alternative; boundary=e89a8f6472e5384c6f04b49c879e

--e89a8f6472e5384c6f04b49c879e
Content-Type: text/plain; charset=ISO-8859-1

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

--e89a8f6472e5384c6f04b49c879e--