Re: Very slow query

[Sven Boden]

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.