Return-Path: 
 <user-java-return-6398-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 47710 invoked from network); 17 Mar 2006 17:14:12 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 17 Mar 2006 17:14:12 -0000
Received: (qmail 72635 invoked by uid 500); 17 Mar 2006 17:14:10 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 72616 invoked by uid 500); 17 Mar 2006 17:14:10 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 72604 invoked by uid 99); 17 Mar 2006 17:14:10 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Mar 2006 09:14:10 -0800
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=HTML_MESSAGE
X-Spam-Check-By: apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Received: from [194.51.201.22] (HELO esa-av-smtp1.gmessaging.net)
 (194.51.201.22)
    by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 17 Mar 2006 09:14:09 -0800
Received: from esa-spas1.gmessaging.net (relay [172.24.21.38])
 by esa-av-smtp1.gmessaging.net
 (iPlanet Messaging Server 5.2 Patch 2 (built Jul 14 2004))
 with ESMTP id <0IWA00KLR7V08I@esa-av-smtp1.gmessaging.net> for
 user-java@ibatis.apache.org; Fri, 17 Mar 2006 18:13:48 +0100 (MET)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by esa-spas1.gmessaging.net (Postfix) with ESMTP id 0E161229DB2	for
 <user-java@ibatis.apache.org>; Fri, 17 Mar 2006 18:13:48 +0100 (CET)
Received: from esa-spas1.gmessaging.net ([127.0.0.1])
 by localhost (esa-spas1 [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id 18282-10 for <user-java@ibatis.apache.org>; Fri,
 17 Mar 2006 18:13:46 +0100 (CET)
Received: from sciops-mailgw.vilspa.esa.int
 (sciops-mailgw.vilspa.esa.int [193.147.152.105])	by esa-spas1.gmessaging.net
 (Postfix) with ESMTP id 5367D229DA9	for <user-java@ibatis.apache.org>; Fri,
 17 Mar 2006 18:13:46 +0100 (CET)
Received: from iso.vilspa.esa.es (isous5.vilspa.esa.es [193.147.153.100])
	by sciops-mailgw.vilspa.esa.int (Postfix) with ESMTP id 3262F11F8F0	for
 <user-java@ibatis.apache.org>; Fri, 17 Mar 2006 18:13:46 +0100 (CET)
Received: from isol16.vilspa.esa.int
 (IDENT:U2FsdGVkX1+l1Bn2OnVtxnlWsdzW/Vjt/h/PCiPbu58@isol16.vilspa.esa.int
 [193.147.153.51])	by iso.vilspa.esa.es (8.12.10/8.12.10)
 with ESMTP id k2HHDkld007084	for <user-java@ibatis.apache.org>; Fri,
 17 Mar 2006 18:13:46 +0100 (MET)
Date: Fri, 17 Mar 2006 18:13:45 +0100
From: Nicolas Fajersztejn <Nicolas.Fajersztejn@sciops.esa.int>
Subject: Very slow query
To: user-java@ibatis.apache.org
Message-id: <1142615625.13224.34.camel@isol16.vilspa.esa.int>
MIME-version: 1.0
X-Mailer: Evolution 2.0.2 (2.0.2-22)
Content-type: multipart/alternative;
 boundary="Boundary_(ID_/YVEcBqqaZWMdt0sV0fosg)"
X-Spam-Score: Content analysis details:   (-1.1 points,
 4.5 points required to be considered as spam) 0.1 HTML_MESSAGE
           BODY:
 HTML included in message -1.2 BAYES_00               BODY: Bayesian spam
 probability is 0 to 1%                            [score: 0.0000]
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N


--Boundary_(ID_/YVEcBqqaZWMdt0sV0fosg)
Content-type: text/plain
Content-transfer-encoding: 7BIT

Hi,

I'm new to iBatis. I have been using it for a couple of months and
really like it.

However, now I am having problems with a very simple query. This is the
mapping I have:

<select id="getExposuresForObsId"
resultClass="xat.proprietarydates.objects.Exposure">
   SELECT
    obsid obsID,
    inst inst,
    epic_filter epicFilter
FROM xsa..exposures
WHERE 
            obsid = #value# 
</select>


obsid is a numeric value. I am passing an Integer as parameter and this
query takes about 1400 ms to execute!!!
The same query run with explicit value (obsid = 100 for example) takes
only 48 ms.

Does it really take so much time to convert an Integer and build up the
query or am I missing something?

This is my config file in case it helps:

    <transactionManager type="JDBC">
      <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="${driver}"/>
      <property name="JDBC.ConnectionURL" value="${url}"/>
      <property name="JDBC.Username" value="${username}"/>
      <property name="JDBC.Password" value="${password}"/>
      <property name="JDBC.DefaultAutocommit" value="true"/>
      <!-- The following are optional -->
      <property name="Pool.MaximumActiveconnections" value="10"/>
      <property name="Pool.MaximumIdleConnections" value="5"/>
      <property name="Pool.MaximumCheckoutTime" value="120000"/>
      <property name="Pool.TimeToWait" value="10000"/>
      <property name="Pool.PingQuery" value="select * from data_set"/>
      <property name="Pool.PingEnabled" value="false"/>
      <property name="Pool.PingConnectionOlderThan" value="0"/>
      <property name="Pool.PingConnectionNotUsedFor" value="0"/>
      </dataSource>
    </transactionManager>

<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="32"
maxSessions="10"
maxTransactions="5"
useStatementNamespaces="false"
/>


And the Java code:

    public static List getExposuresForObsId(Integer obsID) {
        SqlMapClient sqlMap = XATSqlConfig.getSqlMapInstance();
        List exposureList = null;
        try {
            long t1 = System.currentTimeMillis();
            exposureList = sqlMap.queryForList("getExposuresForObsId",
obsID);
            long t2 = System.currentTimeMillis();
            System.out.println("time queryForList is " + (t2-
t1));              
        }                                   
        catch (SQLException e) {
            logger.log(Level.WARNING, "SQLException getting exposures
list: " + e);
        }
        return exposureList;
    }



I would gladly appreciate your help.

Thanks.





--Boundary_(ID_/YVEcBqqaZWMdt0sV0fosg)
Content-type: text/html; charset=utf-8
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.3.2">
</HEAD>
<BODY>
Hi,<BR>
<BR>
I'm new to iBatis. I have been using it for a couple of months and really like it.<BR>
<BR>
However, now I am having problems with a very simple query. This is the mapping I have:<BR>
<BR>
&lt;select id=&quot;getExposuresForObsId&quot; resultClass=&quot;xat.proprietarydates.objects.Exposure&quot;&gt;<BR>
&nbsp;&nbsp; SELECT<BR>
&nbsp;&nbsp;&nbsp; obsid obsID,<BR>
&nbsp;&nbsp;&nbsp; inst inst,<BR>
&nbsp;&nbsp;&nbsp; epic_filter epicFilter<BR>
FROM xsa..exposures<BR>
WHERE <BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; obsid = #value# <BR>
&lt;/select&gt;<BR>
<BR>
<BR>
obsid is a numeric value. I am passing an Integer as parameter and this query takes about 1400 ms to execute!!!<BR>
The same query run with explicit value (obsid = 100 for example) takes only 48 ms.<BR>
<BR>
Does it really take so much time to convert an Integer and build up the query or am I missing something?<BR>
<BR>
This is my config file in case it helps:<BR>
<BR>
&nbsp;&nbsp;&nbsp; &lt;transactionManager type=&quot;JDBC&quot;&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;dataSource type=&quot;SIMPLE&quot;&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;JDBC.Driver&quot; value=&quot;${driver}&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;JDBC.ConnectionURL&quot; value=&quot;${url}&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;JDBC.Username&quot; value=&quot;${username}&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;JDBC.Password&quot; value=&quot;${password}&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;JDBC.DefaultAutocommit&quot; value=&quot;true&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;!-- The following are optional --&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.MaximumActiveconnections&quot; value=&quot;10&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.MaximumIdleConnections&quot; value=&quot;5&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.MaximumCheckoutTime&quot; value=&quot;120000&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.TimeToWait&quot; value=&quot;10000&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.PingQuery&quot; value=&quot;select * from data_set&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.PingEnabled&quot; value=&quot;false&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.PingConnectionOlderThan&quot; value=&quot;0&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;property name=&quot;Pool.PingConnectionNotUsedFor&quot; value=&quot;0&quot;/&gt;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;/dataSource&gt;<BR>
&nbsp;&nbsp;&nbsp; &lt;/transactionManager&gt;<BR>
<BR>
&lt;settings<BR>
cacheModelsEnabled=&quot;true&quot;<BR>
enhancementEnabled=&quot;true&quot;<BR>
lazyLoadingEnabled=&quot;true&quot;<BR>
maxRequests=&quot;32&quot;<BR>
maxSessions=&quot;10&quot;<BR>
maxTransactions=&quot;5&quot;<BR>
useStatementNamespaces=&quot;false&quot;<BR>
/&gt;<BR>
<BR>
<BR>
And the Java code:<BR>
<BR>
&nbsp;&nbsp;&nbsp; public static List getExposuresForObsId(Integer obsID) {<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SqlMapClient sqlMap = XATSqlConfig.getSqlMapInstance();<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List exposureList = null;<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; try {<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; long t1 = System.currentTimeMillis();<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; exposureList = sqlMap.queryForList(&quot;getExposuresForObsId&quot;, obsID);<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; long t2 = System.currentTimeMillis();<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.out.println(&quot;time queryForList is &quot; + (t2-t1));&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; catch (SQLException e) {<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; logger.log(Level.WARNING, &quot;SQLException getting exposures list: &quot; + e);<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return exposureList;<BR>
&nbsp;&nbsp;&nbsp; }<BR>
<BR>
<BR>
<BR>
I would gladly appreciate your help.<BR>
<BR>
Thanks.<BR>
<BR>
<BR>
<BR>
<BR>
</BODY>
</HTML>

--Boundary_(ID_/YVEcBqqaZWMdt0sV0fosg)--