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:[EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 2:43
PM
To: [email protected]
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
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.
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >
> >
> >
> >
>
>
