Return-Path: Delivered-To: apmail-ibatis-user-java-archive@www.apache.org Received: (qmail 99783 invoked from network); 21 Mar 2006 16:19:50 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 21 Mar 2006 16:19:50 -0000 Received: (qmail 83537 invoked by uid 500); 21 Mar 2006 16:19:39 -0000 Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org Received: (qmail 83463 invoked by uid 500); 21 Mar 2006 16:19:39 -0000 Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user-java@ibatis.apache.org Delivered-To: mailing list user-java@ibatis.apache.org Received: (qmail 83377 invoked by uid 99); 21 Mar 2006 16:19:38 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 21 Mar 2006 08:19:38 -0800 X-ASF-Spam-Status: No, hits=0.3 required=10.0 tests=HTML_MESSAGE,MAILTO_TO_SPAM_ADDR X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [83.247.21.91] (HELO wis.nl) (83.247.21.91) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 21 Mar 2006 08:19:36 -0800 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C64D03.2A8AE868" Subject: RE: Very slow query X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Date: Tue, 21 Mar 2006 17:18:58 +0100 Message-ID: <50CA25BD6EEA954FA592C097399942E30E4638DF@CM1.wis.local> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Very slow query Thread-Index: AcZNAMDXdy3hl8RNQF2mdVTJ+IP/LwAAekqg From: "Niels Beekman" To: X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N This is a multi-part message in MIME format. ------_=_NextPart_001_01C64D03.2A8AE868 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable Ok, could you modify the JDBC code to use a PreparedStatement and post the execution times? The way you now execute the query bypasses parameter conversion, so you are probably right that the problem is exactly there. =20 What you could do to verify this is to use the following query in SQLMaps: =20 SELECT obsid obsID, inst inst, epic_filter epicFilter FROM xsa..exposures WHERE obsid =3D $value$ =20 This instructs iBATIS to paste the value in the query like you do, instead of using it as a parameter to PreparedStatement. =20 Hope this clarifies things a bit. =20 Niels =20 ________________________________ From: Nicolas Fajersztejn [mailto:Nicolas.Fajersztejn@sciops.esa.int]=20 Sent: dinsdag 21 maart 2006 17:01 To: user-java@ibatis.apache.org Subject: RE: Very slow query =20 Here is the code. It usually returns between 0 and 20 rows. The whole method executes in 6-10 ms depending on the rows returned. private boolean allEPICFiltersClosedOrCalclosed(int obsID) throws SQLException { long t1 =3D System.currentTimeMillis(); =20 boolean filtersClosedOrCalclosed =3D true; // Select all the entries in the EXPOSURES table from the DB for this // observation ID; Statement stmt =3D this.connection.createStatement(); ResultSet rs =3D stmt.executeQuery("SELECT " + ExposuresTable.COL_OBSID + "," + ExposuresTable.COL_INST + "," + ExposuresTable.COL_EPICFILTER + " FROM " + ExposuresTable.TABLE_NAME + " WHERE " + ExposuresTable.COL_OBSID + " =3D " + obsID); Exposure exp; // Fetch each row from the result set while (rs.next()) { exp =3D new Exposure(); exp.setObsID(rs.getInt(ExposuresTable.COL_OBSID)); exp.setInst(rs.getString(ExposuresTable.COL_INST)); =20 exp.setEpicFilter(rs.getString(ExposuresTable.COL_EPICFILTER)); // We need to decide the value returned following this rule: // - true -> all the EPIC filters must be CLOSED or CAL_CLOSED. // - false -> otherwise. if (exp.getEpicFilter() !=3D null && !exp.getEpicFilter().equals(Exposure.EPIC_FILTER_CLOSED) && !exp.getEpicFilter().equals(Exposure.EPIC_FILTER_CALCLOSED)) { filtersClosedOrCalclosed =3D false; break; } } long t2 =3D System.currentTimeMillis(); System.out.println("time is " + (t2-t1)); =20 =20 return filtersClosedOrCalclosed; } On Tue, 2006-03-21 at 16:49 +0100, Niels Beekman wrote:=20 =20 Can we see the full code of your JDBC performance measurement and give us an indication about the amount of rows the query returns? =20 Have you tried to explicitly define the parameterClass, using 'decimal' or 'java.math.BigDecimal'? =20 Niels =20 -----Original Message----- From: Nicolas Fajersztejn [mailto:Nicolas.Fajersztejn@sciops.esa.int]=20 Sent: dinsdag 21 maart 2006 16:01 To: user-java@ibatis.apache.org Subject: Re: Very slow query =20 Still taking the same time.=20 =20 I tried other query passing a String parameter and it takes about 400ms (which is considerably less than 1500ms) but seems a lot of time to me anyway. =20 With JDBC it takes 8 ms!!! =20 How long is it suppose to take this conversion with iBatis??? Has anybody experienced similar problems with parameter conversion??? =20 =20 =20 On Tue, 2006-03-21 at 07:42 -0700, Nathan Maves wrote: > hmmm... did you try to specify the JDBC type on that column? >=20 >=20 > obsid =3D #obsid:NUMERIC# >=20 >=20 > I am running out of ideas :) >=20 >=20 > Nathan >=20 > On Mar 21, 2006, at 7:25 AM, Nicolas Fajersztejn wrote: >=20 > > Hi Nathan, > >=20 > > the obsid type is Numeric(6,0) (I'm using a Sybase DB but I think > > it maps to the number(p,s) type in Orcacle) > >=20 > > CREATE TABLE dbo.exposures (=20 > > obsid numeric(6,0) NOT NULL, > > inst varchar(5) NOT NULL, > > expno numeric(5,0) NOT NULL, > > expid char(4) NOT NULL, > > datamode varchar(20) NOT NULL, > > start_utc datetime NOT NULL, > > end_utc datetime NOT NULL, > > duration int NOT NULL, > > ocb int NULL, > > epic_filter varchar(16) NULL, > > om_filter varchar(8) NULL, > > bkg_count_rate float NULL, > > scientific char(1) DEFAULT 'Y' NULL, > > duty_cycle float NULL=20 > > ) > >=20 > >=20 > > What I pass to the query is an Integer: > >=20 > >=20 > > SELECT > > obsid obsID, > > inst inst, > > epic_filter epicFilter > > FROM xsa..exposures > > WHERE=20 > > obsid =3D #value# > >=20 > >=20 > >=20 > > public static List getExposuresForObsId(Integer obsID) { > > SqlMapClient sqlMap =3D XATSqlConfig.getSqlMapInstance(); > > List exposureList =3D null; > > try { > > long t1 =3D System.currentTimeMillis(); > > exposureList =3D sqlMap.queryForList > > ("getExposuresForObsId", obsID); > > long t2 =3D System.currentTimeMillis(); > > System.out.println("time queryForList is " + (t2- > > t1)); =20 > > } =20 > > catch (SQLException e) { > > logger.log(Level.WARNING, "SQLException getting > > exposures list: " + e); > > } > > return exposureList; > > } > >=20 > >=20 > >=20 > > Is there another type or a better way of doing this? > >=20 > > Cheers, > > Nicolas. > >=20 > >=20 > >=20 > >=20 > >=20 > >=20 > > On Tue, 2006-03-21 at 07:06 -0700, Nathan Maves wrote: > > > Nicolas, > > >=20 > > >=20 > > > I am guessing that there is a type mismatch from your db to your > > > parameter. Please post both the table definition and your bean. > > >=20 > > >=20 > > > if the DB field is a VARCHAR and your parameter is an Integer it > > > can cause problems. > > >=20 > > >=20 > > > id =3D '123' will have a different performance then id =3D 123 > > >=20 > > >=20 > > > If that is the case then use some thing like > > >=20 > > >=20 > > > id =3D #id:VARCHAR# > > >=20 > > >=20 > > > to insure that quotes are used with a varchar column. > > >=20 > > >=20 > > >=20 > > >=20 > > > Nathan > > >=20 > > > On Mar 21, 2006, at 5:43 AM, Nicolas Fajersztejn wrote: > > >=20 > > > > Hi again, > > > >=20 > > > > I have tried all these things you are suggesting: > > > > - setting cacheModelsEnabled to false > > > > - run the query several times in a loop > > > > - disable logging. > > > >=20 > > > > and still I get the same result. > > > >=20 > > > >=20 > > > > But to me this question is very simple. If I change #value# for > > > > the actual number (i.e. 100) in the query > > > >=20 > > > > SELECT > > > > obsid obsID, > > > > inst inst, > > > > epic_filter epicFilter > > > > FROM xsa..exposures > > > > WHERE=20 > > > > obsid =3D 100 =20 > > > >=20 > > > > (rather than obsid=3D#value#) > > > >=20 > > > > it takes only 50ms rather than 1500ms. So, if I am passing an > > > > Integer it seems very clear to me that all this time is spent in > > > > the conversion of the parameter into the actual value. Isn't > > > > that correct? > > > >=20 > > > > Have you guys tried to measure how long it takes in your > > > > applications to do this type of conversion? It seems very weird > > > > to me that it can take so long just a simple thing. I noticed > > > > because I need to execute this query about 1000 times for > > > > different obsid's and it is obviously not an acceptable time to > > > > wait 1500 seconds. With straight JDBC it only takes 8ms per > > > > query... > > > >=20 > > > > Any suggestions or comments? > > > >=20 > > > > Thanks, > > > > Nicolas. > > > >=20 > > > >=20 > > > >=20 > > > >=20 > > > > On Mon, 2006-03-20 at 09:04 -0700, Clinton Begin wrote: > > > > >=20 > > > > > Also make sure logging is disabled. > > > > >=20 > > > > > Clinton > > > > >=20 > > > > > On 3/17/06, Larry Meadors wrote: > > > > > Also, run the query several times, because on the > > > > > first pass, it is > > > > > setting up the parameterMap.=20 > > > > > =20 > > > > > long t1,t2; > > > > > t1 =3D System.currentTimeMillis(); > > > > > for(int i =3D 0; i++; i < 10) sqlMap.queryForList > > > > > ("getExposuresForObsId", obsID); > > > > > t2 =3D System.currentTimeMillis(); > > > > > System.out.println("time to run queryForList 10x is " > > > > > + (t2-t1));=20 > > > > > =20 > > > > > Larry > > > > > =20 > > > > > =20 > > > > > On 3/17/06, Sven Boden wrote: > > > > > > > > > > > > The pool query is not used as Pool.PingEnabled is > > > > > false. ;-) > > > > > > > > > > > > Try rerunning it setting cacheModelsEnabled to false > > > > > (for a performance=20 > > > > > > run, in a real system if you have a cache hit the > > > > > cache speeds up > > > > > > processing, for a single run a cache slows down), > > > > > and switch off > > > > > > debugging if you have it on. > > > > > > > > > > > > Regards, > > > > > > Sven=20 > > > > > > > > > > > > Larry Meadors wrote: > > > > > > > > > > > > > > > > > from data_set"/> > > > > > > > > > > > > > >What is "data_set", and how long does "select * > > > > > from data_set" take to run?=20 > > > > > > > > > > > > > >Larry > > > > > > > > > > > > > > > > > > > > >On 3/17/06, Nicolas Fajersztejn > > > > > wrote: > > > > > > > > > > > > > > > > > > > > >> 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=20 > > > > > > >>mapping I have: > > > > > > >> > > > > > > >> =20 > > > > > > >> > > > > > > >> > > > > > > >> 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 =3D > > > > > 100 for example) takes only=20 > > > > > > >>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:=20 > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > value=3D"${driver}"/>=20 > > > > > > >> > > > > value=3D"${url}"/> > > > > > > >> > > > > value=3D"${username}"/> > > > > > > >> > > > > value=3D"${password}"/> > > > > > > >> > > > > value=3D"true"/> > > > > > > >> =20 > > > > > > >> > > > > name=3D"Pool.MaximumActiveconnections" value=3D"10"/> > > > > > > >> > > > > name=3D"Pool.MaximumIdleConnections" value=3D"5"/> > > > > > > >> > > > > value=3D"120000"/> > > > > > > >> > > > > value=3D"10000"/> > > > > > > >> > > > > value=3D"select * from data_set"/>=20 > > > > > > >> > > > > value=3D"false"/> > > > > > > >> > > > > name=3D"Pool.PingConnectionOlderThan" value=3D"0"/> > > > > > > >> > > > > Pool.PingConnectionNotUsedFor" value=3D"0"/> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > >> cacheModelsEnabled=3D"true"=20 > > > > > > >> enhancementEnabled=3D"true" > > > > > > >> lazyLoadingEnabled=3D"true" > > > > > > >> maxRequests=3D"32" > > > > > > >> maxSessions=3D"10" > > > > > > >> maxTransactions=3D"5"=20 > > > > > > >> useStatementNamespaces=3D"false" > > > > > > >> /> > > > > > > >> > > > > > > >> > > > > > > >> And the Java code: > > > > > > >> > > > > > > >> public static List getExposuresForObsId > > > > > (Integer obsID) {=20 > > > > > > >> SqlMapClient sqlMap =3D > > > > > XATSqlConfig.getSqlMapInstance(); > > > > > > >> List exposureList =3D null; > > > > > > >> try { > > > > > > >> long t1 =3D System.currentTimeMillis > > > > > (); > > > > > > >> exposureList =3D sqlMap.queryForList > > > > > ("getExposuresForObsId", > > > > > > >>obsID); > > > > > > >> long t2 =3D = System.currentTimeMillis(); > > > > > > >> System.out.println("time queryForList > > > > > is " + (t2-t1)); > > > > > > >> > > > > > > >> } > > > > > > >> catch (SQLException e) { > > > > > > >> logger.log(Level.WARNING, > > > > > "SQLException getting exposures list:=20 > > > > > > >>" + e); > > > > > > >> } > > > > > > >> return exposureList; > > > > > > >> } > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> I would gladly appreciate your help.=20 > > > > > > >> > > > > > > >> Thanks. > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >=20 > > > > > > > > > > >=20 > > >=20 > > >=20 >=20 >=20 =20 =20 ------_=_NextPart_001_01C64D03.2A8AE868 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Ok, could you modify the JDBC code = to use a PreparedStatement and post the execution times? The way you now = execute the query bypasses parameter conversion, so you are probably right that the = problem is exactly there.

 

What you could do to verify this is = to use the following query in SQLMaps:

 

SELECT obsid obsID, inst inst, = epic_filter epicFilter

FROM = xsa..exposures

WHERE obsid =3D $value$

 

This instructs iBATIS to paste the = value in the query like you do, instead of using it as a parameter to = PreparedStatement.

 

Hope this clarifies things a = bit.

 

Niels

 

From: = Nicolas Fajersztejn [mailto:Nicolas.Fajersztejn@sciops.esa.int]
Sent: dinsdag 21 maart = 2006 17:01
To: user-java@ibatis.apache.org
Subject: RE: Very slow = query

 

Here is the code. It usually returns between 0 and 20 rows.
The whole method executes in 6-10 ms depending on the rows returned.


    private boolean allEPICFiltersClosedOrCalclosed(int = obsID) throws SQLException
    {
        long t1 =3D = System.currentTimeMillis();       
        boolean = filtersClosedOrCalclosed =3D true;

        // Select all the entries in = the EXPOSURES table from the DB for this
        // observation ID;
        Statement stmt =3D this.connection.createStatement();
        ResultSet rs =3D stmt.executeQuery("SELECT " + ExposuresTable.COL_OBSID + ","
            &= nbsp;   + ExposuresTable.COL_INST + "," + = ExposuresTable.COL_EPICFILTER
            &= nbsp;   + " FROM " + ExposuresTable.TABLE_NAME + " WHERE = "
            &= nbsp;   + ExposuresTable.COL_OBSID + " =3D " + obsID);

        Exposure exp;
        // Fetch each row from the = result set
        while (rs.next())
        {
            exp = =3D new Exposure();
            exp.setObsID(rs.getInt(ExposuresTable.COL_OBSID));
            exp.setInst(rs.getString(ExposuresTable.COL_INST));
            exp.setEpicFilter(rs.getString(ExposuresTable.COL_EPICFILTER));

            // We = need to decide the value returned following this rule:
            // - = true -> all the EPIC filters must be CLOSED or CAL_CLOSED.
            // - = false -> otherwise.
            if (exp.getEpicFilter() !=3D null
            &= nbsp;       && !exp.getEpicFilter().equals(Exposure.EPIC_FILTER_CLOSED)
            &= nbsp;       && = !exp.getEpicFilter().equals(Exposure.EPIC_FILTER_CALCLOSED))
            {
            &= nbsp;   filtersClosedOrCalclosed =3D false;
            &= nbsp;   break;
            }
        }
        long t2 =3D System.currentTimeMillis();
        System.out.println("time = is " + (t2-t1));       
       
        return = filtersClosedOrCalclosed;
    }




On Tue, 2006-03-21 at 16:49 +0100, Niels = Beekman wrote: 

 
Can we see the full code of your =
JDBC performance measurement and =
give
us an indication about the amount =
of rows the query returns?
 
Have you tried to explicitly =
define the parameterClass, using =
'decimal'
or =
'java.math.BigDecimal'?
 
Niels
 
-----Original =
Message-----
From: Nicolas Fajersztejn =
[mailto:Nicolas.Fajersztejn@sc=
iops.esa.int] 
Sent: dinsdag 21 maart 2006 =
16:01
To: user-java@ibatis.apache.org
Subject: Re: Very slow =
query
 
Still taking the same time. =

 
I tried other query passing a =
String parameter and it takes about =
400ms
(which is considerably less than =
1500ms) but seems a lot of time to =
me
anyway.
 
With JDBC it takes 8 =
ms!!!
 
How long is it suppose to take =
this conversion with iBatis??? =
Has
anybody experienced similar =
problems with parameter =
conversion???
 
 
 
On Tue, 2006-03-21 at 07:42 =
-0700, Nathan Maves wrote:
> hmmm... did you try to =
specify the JDBC type on that =
column?
> =

> =

> obsid =3D =
#obsid:NUMERIC#
> =

> =

> I am running out of ideas =
:)
> =

> =

> =
Nathan
> =

> On Mar 21, 2006, at 7:25 AM, =
Nicolas Fajersztejn wrote:
> =

> > Hi =
Nathan,
> > =

> > the obsid type is =
Numeric(6,0)  (I'm using a Sybase DB but I =
think
> > it maps to the =
number(p,s) type in Orcacle)
> > =

> > CREATE TABLE =
dbo.exposures ( 
> >     =
obsid         numeric(6,0) NOT =
NULL,
> >     =
inst          varchar(5) =
NOT NULL,
> >     =
expno         numeric(5,0) NOT =
NULL,
> >     =
expid         char(4) NOT =
NULL,
> >     =
datamode      varchar(20) NOT =
NULL,
> >     =
start_utc     datetime NOT =
NULL,
> >     =
end_utc       datetime NOT =
NULL,
> >     =
duration      int NOT =
NULL,
> >     =
ocb           int =
NULL,
> >     =
epic_filter   varchar(16) =
NULL,
> >     =
om_filter     varchar(8) =
NULL,
> >     =
bkg_count_rate float NULL,
> >     =
scientific    char(1) DEFAULT 'Y' =
NULL,
> >     =
duty_cycle    float NULL =

> >     =
)
> > =

> > =

> > What I pass to the =
query is an Integer:
> > =

> > =

> > =
SELECT
> >     =
obsid obsID,
> >     =
inst inst,
> >     =
epic_filter epicFilter
> > FROM =
xsa..exposures
> > WHERE =

> =
>           &nb=
sp; obsid =3D #value#
> > =

> > =

> > =

> >     =
public static List getExposuresForObsId(Integer obsID) =
{
> =
>         SqlMapClient sqlMap =
=3D =
XATSqlConfig.getSqlMapInstance();
> =
>         List exposureList =
=3D null;
> =
>         try =
{
> =
>           &nb=
sp; long t1 =3D =
System.currentTimeMillis();
> =
>           &nb=
sp; exposureList =3D =
sqlMap.queryForList
> > =
("getExposuresForObsId", =
obsID);
> =
>           &nb=
sp; long t2 =3D =
System.currentTimeMillis();
> =
>           &nb=
sp; System.out.println("time queryForList is " + =
(t2-
> > =
t1));           &n=
bsp;  
> =
>         =
}            =
            &=
nbsp;          =

> =
>         catch (SQLException =
e) {
> =
>           &nb=
sp; logger.log(Level.WARNING, "SQLException =
getting
> > exposures list: " =
+ e);
> =
>         =
}
> =
>         return =
exposureList;
> >     =
}
> > =

> > =

> > =

> > Is there another type =
or a better way of doing this?
> > =

> > =
Cheers,
> > =
Nicolas.
> > =

> > =

> > =

> > =

> > =

> > =

> > On Tue, 2006-03-21 at =
07:06 -0700, Nathan Maves =
wrote:
> > > =
Nicolas,
> > > =

> > > =

> > > I am guessing that =
there is a type mismatch from your db to =
your
> > > parameter.  =
Please post both the table definition and your =
bean.
> > > =

> > > =

> > > if the DB field is =
a VARCHAR and your parameter is an Integer =
it
> > > can cause =
problems.
> > > =

> > > =

> > > id =3D '123' will =
have a different performance then id =3D =
123
> > > =

> > > =

> > > If that is the =
case then use some thing like
> > > =

> > > =

> > > id =3D =
#id:VARCHAR#
> > > =

> > > =

> > > to insure that =
quotes are used with a varchar =
column.
> > > =

> > > =

> > > =

> > > =

> > > =
Nathan
> > > =

> > > On Mar 21, 2006, =
at 5:43 AM, Nicolas Fajersztejn =
wrote:
> > > =

> > > > Hi =
again,
> > > > =

> > > > I have tried =
all these things you are =
suggesting:
> > > > - setting =
cacheModelsEnabled to false
> > > > - run the =
query several times in a loop
> > > > - disable =
logging.
> > > > =

> > > > and still I =
get the same result.
> > > > =

> > > > =

> > > > But to me =
this question is very simple. If I change #value# =
for
> > > > the actual =
number (i.e. 100) in the query
> > > > =

> > > > =
SELECT
> > > =
>     obsid =
obsID,
> > > =
>     inst =
inst,
> > > =
>     epic_filter =
epicFilter
> > > > FROM =
xsa..exposures
> > > > WHERE =

> > > =
>           &nb=
sp; obsid =3D 100    =

> > > > =

> > > > (rather than =
obsid=3D#value#)
> > > > =

> > > > it takes only =
50ms rather than 1500ms. So, if I am passing =
an
> > > > Integer it =
seems very clear to me that all this time is spent =
in
> > > > the =
conversion of the parameter into the actual value. =
Isn't
> > > > that =
correct?
> > > > =

> > > > Have you guys =
tried to measure how long it takes in =
your
> > > > applications =
to do this type of conversion? It seems very =
weird
> > > > to me that it =
can take so long just a simple thing. I =
noticed
> > > > because I =
need to execute this query about 1000 times =
for
> > > > different =
obsid's and it is obviously not an acceptable time =
to
> > > > wait 1500 =
seconds. With straight JDBC it only takes 8ms =
per
> > > > =
query...
> > > > =

> > > > Any =
suggestions or comments?
> > > > =

> > > > =
Thanks,
> > > > =
Nicolas.
> > > > =

> > > > =

> > > > =

> > > > =

> > > > On Mon, =
2006-03-20 at 09:04 -0700, Clinton Begin =
wrote:
> > > > > =

> > > > > Also =
make sure logging is disabled.
> > > > > =

> > > > > =
Clinton
=

> > > > > =

> > > > > On =
3/17/06, Larry Meadors <lmeadors@apache.org> =
wrote:
> > > > =
>         Also, run the query =
several times, because on the
> > > > =
>         first pass, it =
is
> > > > =
>         setting up the =
parameterMap. 
> > > > =
>         =

> > > > =
>         long =
t1,t2;
> > > > =
>         t1 =3D =
System.currentTimeMillis();
> > > > =
>         for(int i =3D 0; =
i++; i < 10) =
sqlMap.queryForList
> > > > =
>         =
("getExposuresForObsId", =
obsID);
> > > > =
>         t2 =3D =
System.currentTimeMillis();
> > > > =
>         =
System.out.println("time to run queryForList 10x is =
"
> > > > =
>         + (t2-t1)); =

> > > > =
>         =

> > > > =
>         =
Larry
> > > > =
>         =

> > > > =
>         =

> > > > =
>         On 3/17/06, Sven =
Boden <list123@pandora.be> =
wrote:
> > > > =
>         =
>
> > > > =
>         > The pool query =
is not used as Pool.PingEnabled =
is
> > > > =
>         false. =
;-)
> > > > =
>         =
>
> > > > =
>         > Try rerunning =
it setting cacheModelsEnabled to =
false
> > > > =
>         (for a performance =

> > > > =
>         > run, in a real =
system if you have a cache hit =
the
> > > > =
>         cache speeds =
up
> > > > =
>         > processing, =
for a single run a cache slows =
down),
> > > > =
>         and switch =
off
> > > > =
>         > debugging if =
you have it on.
> > > > =
>         =
>
> > > > =
>         > =
Regards,
> > > > =
>         > Sven =

> > > > =
>         =
>
> > > > =
>         > Larry Meadors =
wrote:
> > > > =
>         =
>
> > > > =
>         > =
><property name=3D"Pool.PingQuery" value=3D"select =
*
> > > > =
>         from =
data_set"/>
> > > > =
>         > =
>
> > > > =
>         > >What is =
"data_set", and how long does "select =
*
> > > > =
>         from data_set" =
take to run? 
> > > > =
>         > =
>
> > > > =
>         > =
>Larry
> > > > =
>         > =
>
> > > > =
>         > =
>
> > > > =
>         > >On =
3/17/06, Nicolas Fajersztejn
> > > > =
>         <Nicolas.Fajersztejn@sc=
iops.esa.int> wrote:
> > > > =
>         > =
>
> > > > =
>         > =
>
> > > > =
>         > >> =
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=3D"getExposuresForObsId"
=
> > > > =
>         =
>
> > > > =
>         =
>>resultClass=3D"xat.proprietarydates.objects.Exposure"&g=
t;
> > > > =
>         > =
>>    SELECT =

> > > > =
>         > =
>>     obsid =
obsID,
> > > > =
>         > =
>>     inst =
inst,
> > > > =
>         > =
>>     epic_filter =
epicFilter
> > > > =
>         > >> FROM =
xsa..exposures
> > > > =
>         > >> =
WHERE
> > > > =
>         > =
>>           =
;  obsid =3D #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 =
=3D
> > > > =
>         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=3D"JDBC">
> > > > =
>         > =
>>       <dataSource =
type=3D"SIMPLE">
> > > > =
>         > =
>>       <property =
name=3D"JDBC.Driver"
> > > > =
>         =
value=3D"${driver}"/> =

> > > > =
>         > =
>>       <property =
name=3D"JDBC.ConnectionURL"
=
> > > > =
>         =
value=3D"${url}"/>
> > > > =
>         > =
>>       <property =
name=3D"JDBC.Username"
> > > > =
>         =
value=3D"${username}"/>
<=
font
size=3D2 color=3Dblack face=3D"Courier New">> > > > =
>         > =
>>       <property name=3D" =
JDBC.Password"
> > > > =
>         =
value=3D"${password}"/>
<=
font
size=3D2 color=3Dblack face=3D"Courier New">> > > > =
>         > =
>>       <property =
name=3D"JDBC.DefaultAutocommit"
<=
pre>> > > > =
>      =
   value=3D"true"/><=
/pre>
> > > > =
>         > =
>>       <!-- The following are =
optional --> 
> > > > =
>         > =
>>       =
<property
> > > > =
>         =
name=3D"Pool.MaximumActiveconnections" =
value=3D"10"/>
> > > > =
>         > =
>>       =
<property
> > > > =
>         =
name=3D"Pool.MaximumIdleConnections" =
value=3D"5"/>
> > > > =
>         > =
>>       <property name=3D" =
Pool.MaximumCheckoutTime"
> > > > =
>         =
value=3D"120000"/>
> > > > =
>         > =
>>       <property =
name=3D"Pool.TimeToWait"
> > > > =
>         =
value=3D"10000"/>
> > > > =
>         > =
>>       <property =
name=3D"Pool.PingQuery"
> > > > =
>         =
value=3D"select * from data_set"/> =

> > > > =
>         > =
>>       <property =
name=3D"Pool.PingEnabled"
> > > > =
>         =
value=3D"false"/>
> > > > =
>         > =
>>       =
<property
> > > > =
>         =
name=3D"Pool.PingConnectionOlderThan" =
value=3D"0"/>
> > > > =
>         > =
>>       <property =
name=3D"
> > > > =
>         =
Pool.PingConnectionNotUsedFor" =
value=3D"0"/>
> > > > =
>         > =
>>       =
</dataSource>
> > > > =
>         > =
>>     =
</transactionManager>
> > > > =
>         > =
>>
> > > > =
>         > >> =
<settings
> > > > =
>         > >> =
cacheModelsEnabled=3D"true" =

> > > > =
>         > >> =
enhancementEnabled=3D"true"
=
> > > > =
>         > >> =
lazyLoadingEnabled=3D"true"
=
> > > > =
>         > >> =
maxRequests=3D"32"
> > > > =
>         > >> =
maxSessions=3D"10"
> > > > =
>         > >> =
maxTransactions=3D"5" =

> > > > =
>         > >> =
useStatementNamespaces=3D"false"
=

> > > > =
>         > >> =
/>
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > >> And =
the Java code:
> > > > =
>         > =
>>
> > > > =
>         > =
>>     public static List =
getExposuresForObsId
> > > > =
>         (Integer obsID) { =

> > > > =
>         > =
>>         SqlMapClient =
sqlMap =3D
> > > > =
>         =
XATSqlConfig.getSqlMapInstance();
> > > > =
>         > =
>>         List =
exposureList =3D null;
> > > > =
>         > =
>>         try =
{
> > > > =
>         > =
>>           =
;  long t1 =3D =
System.currentTimeMillis
> > > > =
>         =
();
> > > > =
>         > =
>>           =
;  exposureList =3D =
sqlMap.queryForList
> > > > =
>         =
("getExposuresForObsId",
> > > > =
>         > =
>>obsID);
> > > > =
>         > =
>>           =
;  long t2 =3D =
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.
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > =
>>
> > > > =
>         > =
>
> > > > =
>         > =
>
> > > > =
>         > =
>
> > > > =
>         > =
>
> > > > =
>         > =

> > > > =
>         =
>
> > > > > =

> > > =

> > > =

> =

> =

 
 
------_=_NextPart_001_01C64D03.2A8AE868--