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 14:25:22 GMT
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