ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <seyhan.basm...@teb.com.tr>
Subject RE: Very slow query
Date Tue, 21 Mar 2006 13:48:53 GMT
Hi, 

 

Get your running query from sqlarea using 

select * from v$sqlarea where sql_text like '%FROM xsa..exposures%'

 

using an explain plan tool , check your running query whether using
index!!!

 

Probably there is a type mismatch between obsid column and bind
variable..

If so, your query will not use any index.. 

 

If your queiry using index , then check ibatis side..

 

 

 

 

 

________________________________

From: Nicolas Fajersztejn [mailto:Nicolas.Fajersztejn@sciops.esa.int] 
Sent: Tuesday, March 21, 2006 2:43 PM
To: user-java@ibatis.apache.org
Subject: Re: Very slow query

 

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.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
> 
>
 
 
Bu elektronik posta mesaji ve ekleri sadece gonderildigi kisi veya kuruma ozeldir ve gizli
bilgiler icerebilir. Eger bu mesaji hataen aldiysaniz lutfen bu durumu gonderen kisiye derhal
bildiriniz ve mesaji sisteminizden siliniz. Eger dogru kisiye ulasmadigini dusunuyorsaniz,
bu mesajin gizlenmesi, yonlendirilmesi, kopyalanmasi veya herhangi bir sekilde kullanilmasi
yasaktir. Internet iletisiminde guvenlik ve hatasiz gonderim garanti edilemeyeceginden, mesajin
yerine ulasmamasi, gec ulasmasi, iceriginin bozulmasi ya da mesajin virus tasimasi gibi problemler
olusabilir. Gonderen taraf bu tip sorunlardan sorumlu tutulmaz.

This e-mail message and any attachments are confidential and intended solely for the use of
the individual or entity to whom they are addressed. If you have received this message in
error, please notify the sender immediately and delete it from your system. If you are not
the intended recipient you are hereby notified that any dissemination, forwarding, copying
or use of any of the information is prohibited. Internet communications cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost, arrive late
or contain viruses. The sender therefore does not accept liability for any errors or omissions
in the context of this message which arise as a result of Internet transmission.

Mime
View raw message