ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Very slow query
Date Tue, 21 Mar 2006 17:28:09 GMT
If I were going to do it 1000 times, I'd write a stored procedure to do it.
That will be literally several orders of magnitude faster (if all the data
you need is already in the database).

You may be able to use a rowhandler (this does load all of the data, but
only one row at a time is in memory - and it would eliminate 999 calls to
get a connection to the database, prepare the statement, and execute the
preparedstatement).

If that is not possible use JDBC.

Larry


On 3/21/06, Nicolas Fajersztejn <Nicolas.Fajersztejn@sciops.esa.int> wrote:
>
> 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:Nicolas.Fajersztejn@sciops.esa.int]
> > Sent: dinsdag 21 maart 2006 17:37
> > To: user-java@ibatis.apache.org
> > 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:Nicolas.Fajersztejn@sciops.esa.int]
> > Sent: dinsdag 21 maart 2006 17:01
> > To: user-java@ibatis.apache.org
> > 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:Nicolas.Fajersztejn@sciops.esa.int]
> > Sent: dinsdag 21 maart 2006 16:01
> > 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.
> > > > > > >         > >>
> > > > > > >         > >>
> > > > > > >         > >>
> > > > > > >         > >>
> > > > > > >         > >>
> > > > > > >         > >>
> > > > > > >         > >>
> > > > > > >         > >
> > > > > > >         > >
> > > > > > >         > >
> > > > > > >         > >
> > > > > > >         >
> > > > > > >         >
> > > > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
>
>

Mime
View raw message