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, [EMAIL PROTECTED] 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:[EMAIL PROTECTED] > Sent: Tuesday, March 21, 2006 5:01 PM > To: [email protected] > 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 <[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. > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > 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. >
