ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <Nathan.Ma...@Sun.COM>
Subject Re: Very slow query
Date Tue, 21 Mar 2006 14:06:06 GMT
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