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 12:43:13 GMT
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