ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Niels Beekman" <n.beek...@wis.nl>
Subject RE: Very slow query
Date Tue, 21 Mar 2006 15:49:01 GMT
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@sciops.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 = #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 
> >             obsid = #value#
> > 
> > 
> > 
> >     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;
> >     }
> > 
> > 
> > 
> > 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 = '123' will have a different performance then id = 123
> > > 
> > > 
> > > If that is the case then use some thing like
> > > 
> > > 
> > > id = #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 
> > > >             obsid = 100    
> > > > 
> > > > (rather than obsid=#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 = System.currentTimeMillis();
> > > > >         for(int i = 0; i++; i < 10) sqlMap.queryForList
> > > > >         ("getExposuresForObsId", obsID);
> > > > >         t2 = 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="Pool.PingQuery" value="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@sciops.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="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.
> > > > >         > >>
> > > > >         > >>
> > > > >         > >>
> > > > >         > >>
> > > > >         > >>
> > > > >         > >>
> > > > >         > >>
> > > > >         > >
> > > > >         > >
> > > > >         > >
> > > > >         > >
> > > > >         > 
> > > > >         >
> > > > > 
> > > 
> > > 
> 
> 


Mime
View raw message