OK. Another question that came up to my mind:
The query takes now about 50ms, but I know that the SQL execution part takes just 8ms (as JDBC shows). So, I guess the rest of the time is used to open the connection and create the statement and so on... is this true or is most of this time used to build up the query? The question is, how can I cache connections or statements in order to reduce the execution time to the minimum? The 50ms time is acceptable for 1 execution, but if I have to run 1000, it really makes a different 1000*8 vs 1000*50, so would still go for JDBC. I have tried using cacheModelsEnabled="true", but it didn't cause any effect. Cheers, Nicolas. On Tue, 2006-03-21 at 17:41 +0100, Niels Beekman wrote: > The second option, if you execute multiple times (which you do) this > should give best performance, but only if the parameters are correct J > > > > Glad it worked. > > > > Niels > > > > > ______________________________________________________________________ > > From: Nicolas Fajersztejn [mailto:[EMAIL PROTECTED] > Sent: dinsdag 21 maart 2006 17:37 > To: [email protected] > Subject: RE: Very slow query > > > > > > Hi Niels, > > Thanks a lot. It works!!! > > I have tried both of your suggestions: > > - passing an Integer and using $value$ or > - passing BigDecimal and using #value# (there is no conversion done I > guess) > > Both solutions run the query in about 50ms, which I guess is no longer > improvable, is it? > > Which of the 2 solutions should I use? > > Cheers, > Nicolas. > > > On Tue, 2006-03-21 at 17:18 +0100, Niels Beekman wrote: > > > > Ok, could you modify the JDBC code to use a PreparedStatement and post > the execution times? The way you now execute the query bypasses > parameter conversion, so you are probably right that the problem is > exactly there. > > > > What you could do to verify this is to use the following query in > SQLMaps: > > > > SELECT obsid obsID, inst inst, epic_filter epicFilter > > FROM xsa..exposures > > WHERE obsid = $value$ > > > > This instructs iBATIS to paste the value in the query like you do, > instead of using it as a parameter to PreparedStatement. > > > > Hope this clarifies things a bit. > > > > Niels > > > > > ______________________________________________________________________ > > > > From: Nicolas Fajersztejn [mailto:[EMAIL PROTECTED] > Sent: dinsdag 21 maart 2006 17:01 > To: [email protected] > Subject: RE: Very slow query > > > > > 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:[EMAIL PROTECTED] > Sent: dinsdag 21 maart 2006 16:01 > 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. > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > >> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
