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 15:54:48 GMT
Yes, there is unique index on (obsid,expno)
But my point remains the same, without parameter conversion the query
runs in 50ms and with parameter conversion in 1500ms, so I guess there
is where the problem lies.

And I need to run this query ~1000 times (one per obsid), meaning 1000 *
1500ms which is totally unacceptable, and I do not want to load all the
table in memory because it is huge and I won't be using most of the
rows. 




On Tue, 2006-03-21 at 17:28 +0200, seyhan.basmaci@teb.com.tr wrote:
> obsid column defined as primary key or not ?         
> is there any index on it, 
> if not , the number of records for the same obsid can affect response
> time, 
> with jdbc using next() method it may take 8 ms for the fist record ,
> probably ibatis getting all of the matcing records!!!
> 
> 
> 
> -----Original Message-----
> From: Nicolas Fajersztejn [mailto:Nicolas.Fajersztejn@sciops.esa.int] 
> Sent: Tuesday, March 21, 2006 5:01 PM
> 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.
> > > > > >         > >>
> > > > > >         > >>
> > > > > >         > >>
> > > > > >         > >>
> > > > > >         > >>
> > > > > >         > >>
> > > > > >         > >>
> > > > > >         > >
> > > > > >         > >
> > > > > >         > >
> > > > > >         > >
> > > > > >         > 
> > > > > >         >
> > > > > > 
> > > > 
> > > > 
> > 
> >
>  
> 
> Bu elektronik posta mesaji ve ekleri sadece gonderildigi kisi veya kuruma ozeldir ve
gizli bilgiler icerebilir. Eger bu mesaji hataen aldiysaniz lutfen bu durumu gonderen kisiye
derhal bildiriniz ve mesaji sisteminizden siliniz. Eger dogru kisiye ulasmadigini dusunuyorsaniz,
bu mesajin gizlenmesi, yonlendirilmesi, kopyalanmasi veya herhangi bir sekilde kullanilmasi
yasaktir. Internet iletisiminde guvenlik ve hatasiz gonderim garanti edilemeyeceginden, mesajin
yerine ulasmamasi, gec ulasmasi, iceriginin bozulmasi ya da mesajin virus tasimasi gibi problemler
olusabilir. Gonderen taraf bu tip sorunlardan sorumlu tutulmaz.
> 
> This e-mail message and any attachments are confidential and intended solely for the
use of the individual or entity to whom they are addressed. If you have received this message
in error, please notify the sender immediately and delete it from your system. If you are
not the intended recipient you are hereby notified that any dissemination, forwarding, copying
or use of any of the information is prohibited. Internet communications cannot be guaranteed
to be secure or error-free as information could be intercepted, corrupted, lost, arrive late
or contain viruses. The sender therefore does not accept liability for any errors or omissions
in the context of this message which arise as a result of Internet transmission.
> 


Mime
View raw message