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 16:01:19 GMT
Here is the code. It usually returns between 0 and 20 rows.
The whole method executes in 6-10 ms depending on the rows returned.


    private boolean allEPICFiltersClosedOrCalclosed(int obsID) throws
SQLException
    {
        long t1 = System.currentTimeMillis();        
        boolean filtersClosedOrCalclosed = true;

        // Select all the entries in the EXPOSURES table from the DB for
this
        // observation ID;
        Statement stmt = this.connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT " +
ExposuresTable.COL_OBSID + ","
                + ExposuresTable.COL_INST + "," +
ExposuresTable.COL_EPICFILTER
                + " FROM " + ExposuresTable.TABLE_NAME + " WHERE "
                + ExposuresTable.COL_OBSID + " = " + obsID);

        Exposure exp;
        // Fetch each row from the result set
        while (rs.next())
        {
            exp = new Exposure();
            exp.setObsID(rs.getInt(ExposuresTable.COL_OBSID));
            exp.setInst(rs.getString(ExposuresTable.COL_INST));
            exp.setEpicFilter(rs.getString
(ExposuresTable.COL_EPICFILTER));

            // We need to decide the value returned following this rule:
            // - true -> all the EPIC filters must be CLOSED or
CAL_CLOSED.
            // - false -> otherwise.
            if (exp.getEpicFilter() != null
                    && !exp.getEpicFilter().equals
(Exposure.EPIC_FILTER_CLOSED)
                    && !exp.getEpicFilter().equals
(Exposure.EPIC_FILTER_CALCLOSED))
            {
                filtersClosedOrCalclosed = false;
                break;
            }
        }
        long t2 = System.currentTimeMillis();
        System.out.println("time is " + (t2-t1));        
        
        return filtersClosedOrCalclosed;
    }




On Tue, 2006-03-21 at 16:49 +0100, Niels Beekman wrote:

> Can we see the full code of your JDBC performance measurement and give
> us an indication about the amount of rows the query returns?
> 
> Have you tried to explicitly define the parameterClass, using 'decimal'
> or 'java.math.BigDecimal'?
> 
> Niels
> 
> -----Original Message-----
> From: Nicolas Fajersztejn [mailto:Nicolas.Fajersztejn@sciops.esa.int] 
> Sent: dinsdag 21 maart 2006 16:01
> To: user-java@ibatis.apache.org
> Subject: Re: Very slow query
> 
> Still taking the same time. 
> 
> I tried other query passing a String parameter and it takes about 400ms
> (which is considerably less than 1500ms) but seems a lot of time to me
> anyway.
> 
> With JDBC it takes 8 ms!!!
> 
> How long is it suppose to take this conversion with iBatis??? Has
> anybody experienced similar problems with parameter conversion???
> 
> 
> 
> On Tue, 2006-03-21 at 07:42 -0700, Nathan Maves wrote:
> > hmmm... did you try to specify the JDBC type on that column?
> > 
> > 
> > obsid = #obsid:NUMERIC#
> > 
> > 
> > I am running out of ideas :)
> > 
> > 
> > Nathan
> > 
> > On Mar 21, 2006, at 7:25 AM, Nicolas Fajersztejn wrote:
> > 
> > > Hi Nathan,
> > > 
> > > the obsid type is Numeric(6,0)  (I'm using a Sybase DB but I think
> > > it maps to the number(p,s) type in Orcacle)
> > > 
> > > CREATE TABLE dbo.exposures ( 
> > >     obsid         numeric(6,0) NOT NULL,
> > >     inst          varchar(5) NOT NULL,
> > >     expno         numeric(5,0) NOT NULL,
> > >     expid         char(4) NOT NULL,
> > >     datamode      varchar(20) NOT NULL,
> > >     start_utc     datetime NOT NULL,
> > >     end_utc       datetime NOT NULL,
> > >     duration      int NOT NULL,
> > >     ocb           int NULL,
> > >     epic_filter   varchar(16) NULL,
> > >     om_filter     varchar(8) NULL,
> > >     bkg_count_rate float NULL,
> > >     scientific    char(1) DEFAULT 'Y' NULL,
> > >     duty_cycle    float NULL 
> > >     )
> > > 
> > > 
> > > What I pass to the query is an Integer:
> > > 
> > > 
> > > SELECT
> > >     obsid obsID,
> > >     inst inst,
> > >     epic_filter epicFilter
> > > FROM xsa..exposures
> > > WHERE 
> > >             obsid = #value#
> > > 
> > > 
> > > 
> > >     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;
> > >     }
> > > 
> > > 
> > > 
> > > Is there another type or a better way of doing this?
> > > 
> > > Cheers,
> > > Nicolas.
> > > 
> > > 
> > > 
> > > 
> > > 
> > > 
> > > On Tue, 2006-03-21 at 07:06 -0700, Nathan Maves wrote:
> > > > 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