ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nicolas Fajersztejn <Nicolas.Fajerszt...@sciops.esa.int>
Subject Re: Very slow query
Date Tue, 21 Mar 2006 15:00:41 GMT
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