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 <[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.
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >>
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > >
> > >
>
>
