ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clinton Begin" <clinton.be...@gmail.com>
Subject Re: Updating from ibatis 1.3.1 to 2.1.7 .. what about this one .. how to access the resultSet because performance matters
Date Thu, 17 Aug 2006 19:58:40 GMT
>> So creating object for each row of the ResultSet appear to me like an
>> overhead that will hit performance significantly.

I doubt it will be that significant, especially if you use a RowHandler.
This sounds like a case where you could.

You'll never know if you don't try it!  (simple solution first)

Cheers,
Clinton

On 8/17/06, David Gagnon <dgagnon@siunik.com> wrote:
>
> Sure.. maybe you will give me a better way to do my report.  For every
> report I issue a big SQL and go thought the resultSet (wich can have to
> generate SAX events to feed FOP to generate a PDF report.
>
> So I call the SQL read the resultSet and create SAX EVent,  per example,
> for each row:
>
> handler.startElement("accountPayable");
>
>             handler.element("id", rs.getString(1));
>             BigDecimal paid = rs.getBigDecimal(2);
>             handler.element("paid", moneyFormat.format(paid));
>             BigDecimal amount = rs.getBigDecimal(3);
>             handler.element("amount", moneyFormat.format(amount));
>             handler.element("date",
> DateSupport.getDateAsString(language, new MultiFieldDate(rs.getDate(4))));
>             handler.element("dateForcast",
> DateSupport.getDateAsString(language, new MultiFieldDate(rs.getDate
> (15))));
>
>             BigDecimal minus30 = rs.getBigDecimal(5);
>             handler.element("minus30", moneyFormat.format(minus30));
>             BigDecimal between3160 = rs.getBigDecimal(6);
>             handler.element("between3160", moneyFormat.format
> (between3160));
>             BigDecimal between6190 = rs.getBigDecimal(7);
>             handler.element("between6190", moneyFormat.format
> (between6190));
>             BigDecimal plus91 = rs.getBigDecimal(8);
>             handler.element("plus91", moneyFormat.format(plus91));
>             handler.element("totalLine",
> moneyFormat.format(minus30.add(between3160.add
> (between6190).add(plus91))));
>
>             SupplierNode clientNode = (SupplierNode) (parent);
>             clientNode.total30Less = clientNode.total30Less.add(minus30);
>             clientNode.total3160 = clientNode.total3160.add(between3160);
>             clientNode.total6190 = clientNode.total6190.add(between6190);
>             clientNode.total91More = clientNode.total91More.add(plus91);
>
>             handler.endElement("accountPayable");
>
> In this scenario I want to have my SQL statement in the SQLMap, be able
> to use the framework to map parameter. But I want to go though the
> ResulSet my self.  The reports are often huge and take time to create.
> So creating object for each row of the ResultSet appear to me like an
> overhead that will hit performance significantly.
>
> Maybe I'm wrong.  I'm still figuring out how to migrate some of my stuff
> to 2.1.7 so any help, or comments are welcome :-)
>
> David
>
> > Not sure when this was but stored proc calls can use a parameterMap.
> >
> > The issue of using straight JDBC has come up multiple times and
> > everytime, in the end, they see that there is really no reason to.
> > Supply a good reason and you might change my mind.
> >
> > Nathan
> >
> >
> > On 8/17/06, *David Gagnon* <dgagnon@siunik.com
> > <mailto:dgagnon@siunik.com>> wrote:
> >
> >     I do love Ibatis ...  and all my DAOs using it the way it should ..
> of
> >     course you only hear of thing that doesn't work :-)  Like piece of
> >     code
> >     that use StoredProcedure, or function that create report which are
> CPU
> >     intensive.
> >
> >     When I start using stored procedure with Ibatis you were taking
> about
> >     How to implement parameterMap (If I remember well :-)).
> >     So its been a while ago and I need to standardize a bunch of stuff.
> >
> >     And even if I use JDBC directly, I like to store the statement in
> the
> >     map anyway.  I have no SQL hardcoded in the code and all the SQL
> >     are at
> >     the same place (i.e.: in the SqlMap).
> >
> >     For now I found the ExtendedSqlMapClient interface and I will see
> >     if I
> >     cannot use it as an intermediate step (To avoid refactor too much
> >     stuff
> >     at the same time)
> >
> >     Thanks for the help! It's really appreciated!
> >
> >     Best Regard
> >     /David
> >
> >
> >     Clinton Begin wrote:
> >     >
> >     > Wow, I wonder why you're using iBATIS at all.  ;-)
> >     >
> >     > In this case you have three options:
> >     >
> >     > 1) Stop accessing the ResultSet and let iBATIS do its job.
> >     > Performance at that level should not be an issue for you.
> >     >
> >     > 2) Use a RowHandler, it's a bit closer to the ResultSet, so you
> >     can do
> >     > whatever you like with it -- mostly to process row-by-row, so you
> >     > don't have to load them all up into memory.
> >     >
> >     > 3) Bypass iBATIS entirely and just use JDBC for this piece.
> >     >
> >     > Cheers,
> >     > Clinton
> >     >
> >     >
> >     > On 8/17/06, *David Gagnon* <dgagnon@siunik.com
> >     <mailto:dgagnon@siunik.com>
> >     > <mailto:dgagnon@siunik.com <mailto:dgagnon@siunik.com>>>
wrote:
> >     >
> >     >     Hi all,
> >     >
> >     >       Thanks for your help.  Upgrading is a huge job but it goes
> >     well so
> >     >     far.  How can I have access to the ResultSet.  Here I need
> >     performance
> >     >     so I need to access the resultSet directly.  More I have 60+
> >     reports
> >     >     that all access the resultSet directly.
> >     >
> >     >     Thanks for you help!
> >     >
> >     >     Regards
> >     >     /David
> >     >
> >     >
> >     >         public synchronized void processDataChangeNotification() {
> >     >             String sql =
> >     >     dbNotificationDao.getSqlMap
> >     >     ().getMappedStatement(DbNotificationDaoX.NOTIFICATION_PREFIX
> >     >     + "." + WebOsConstants.DB_GET).getSql(null);
> >     >             DbConnection dbConDetail = null;
> >     >             // allow only one at a time
> >     >             try {
> >     >
> >     >                 ConnectionFactory connectionFactory =
> >     (ConnectionFactory)
> >     >     ManagersFactory.getManager(ConnectionFactory.MANAGER_KEY);
> >     >
> >     >                 dbConDetail = connectionFactory.getConnection();
> >     >                 dbConDetail.startTransaction ();
> >     >                 ResultSet detailRs2 = dbConDetail.executeQuery
> (sql);
> >     >                 while (detailRs2.next()) {
> >     >                     String entity = detailRs2.getString(1);
> >     >                     cacheUpdateInfo.id1 = detailRs2.getString (2);
> >     >                     cacheUpdateInfo.id2 = detailRs2.getString(3);
> >     >                     cacheUpdateInfo.id3 = detailRs2.getString(4);
> >     >                     cacheUpdateInfo.id4 = detailRs2.getString(5);
> >     >                     cacheUpdateInfo.timestamp =
> >     detailRs2.getString(6);
> >     >                     CachedDao handler = (CachedDao)
> >     >     dataChangeHandler.get(entity);
> >     >                     if (handler != null) {
> >     >                         handler.handleDataChange(entity,
> >     >     cacheUpdateInfo);
> >     >                     } else {
> >     >                         log.error("No data change handler
> >     register for
> >     >     entity : " + entity);
> >     >                     }
> >     >                 }
> >     >
> >     >                 // delete processed notification
> >     >                 sql =
> >     >
> >     dbNotificationDao.getSqlMap().getMappedStatement(
> DbNotificationDaoX.NOTIFICATION_PREFIX
> >
> >     >     + "." + WebOsConstants.DB_DELETE).getSql(null);
> >     >                 dbConDetail.execute(sql);
> >     >
> >     >                 dbConDetail.commitTransaction();
> >     >
> >     >             } catch (SQLException e) {
> >     >                 log.error("Unable to write the db
> >     (EntitySerialiser): " +
> >     >     e.getMessage(), e);
> >     >             } finally {
> >     >                 if (dbConDetail != null)
> >     >                     dbConDetail.closeAll();
> >     >             }
> >     >
> >     >
> >     >         }
> >     >
> >     >
> >     >
> >     >
> >     >
> >     >     Clinton Begin wrote:
> >     >     > Using setUserConnection will work.   It's thread safe, so
> >     don't
> >     >     worry
> >     >     > about concurrent access.  Just make sure to "unset" it by
> >     calling
> >     >     > setUserConnection (null) -- which reminds me, we should add
> a
> >     >     > clearUserConnection() to make this code nicer.
> >     >     >
> >     >     > The other way to do it is with openSession (Connection
> >     conn).  But I
> >     >     > don't see a reason for you to do that here.
> >     >     >
> >     >     > As for accessing the SQL statements themselves -- be
> >     warned, it will
> >     >     > not be easy.  iBATIS 2.0 does a lot more to keep you away
> >     from the
> >     >     > guts of the framework to avoid upgrade issues...as you're
> >     >     experiencing
> >     >     > now.  ;-)
> >     >     >
> >     >     > Let us know when you get to that point, we'll see what we
> >     can do.
> >     >     >
> >     >     > public final List getList(DbConnection dbCon, String
> >     mapKey, Object
> >     >     > param)
> >     >     >     throws DatabaseException {
> >     >     >   boolean handleTransactionLocally = false;
> >     >     >   try {
> >     >     >     if (dbCon == null) {
> >     >     >       handleTransactionLocally = true;
> >     >     >       dbCon = connectionFactory.getConnectio n();
> >     >     >       dbCon.setReadOnlyConnection(true);
> >     >     >     }
> >     >     >     sqlMap.setUserConnection (dbCon.getConnection());
> >     >     >     return sqlMap.queryForList(mapKey, param);
> >     >     >   } catch (SQLException e) {
> >     >     >     // log.error ("Error during sql: " + e.getMessage(), e);
> >     >     >
> >     >     >     ExceptionAdaptor.instance(exceptionAdaptorKey).getMappe
> >     >     dException(e,
> >     >     >       "getList: " + e.getMessage(), true,
> >     >     > ExceptionAdaptor.ACTION_SEARCH , param);
> >     >     >   } finally {
> >     >     >     sqlMap.setUserConnection (null);
> >     >     >     if (dbCon != null) {
> >     >     >       dbCon.closeAll(handleTransactionLocally);
> >     >     >     }
> >     >     >   }
> >     >     > }
> >     >     >
> >     >     > Clinton
> >     >     >
> >     >     >
> >     >     >
> >     >     >
> >     >     > On 8/16/06, *David Gagnon* <dgagnon@siunik.com
> >     <mailto:dgagnon@siunik.com>
> >     >     <mailto: dgagnon@siunik.com <mailto:dgagnon@siunik.com>>
> >     >     > <mailto:dgagnon@siunik.com <mailto:dgagnon@siunik.com>
> >     <mailto:dgagnon@siunik.com <mailto:dgagnon@siunik.com>>>>
wrote:
> >     >     >
> >     >     >     Hi all,
> >     >     >
> >     >     >     I'm having a lot of fun trying to convert my code to
> >     >     2.1.7.  There is
> >     >     >     still a lot of things I didn`t figured out yet.
> >     >     >
> >     >     >     Can you please tell me how to convert the following
> >     function.?
> >     >     >
> >     >     >
> >     >     >         public final List getList(DbConnection dbCon,
> >     String mapKey,
> >     >     >     Object
> >     >     >     param) throws DatabaseException {
> >     >     >
> >     >     >             List list = null;
> >     >     >             boolean handleTransactionLocally = false;
> >     >     >             try {
> >     >     >                 if (dbCon == null) {
> >     >     >                     handleTransactionLocally = true;
> >     >     >                     dbCon = connectionFactory.getConnection
> ();
> >     >     >                     dbCon.setReadOnlyConnection(true);
> >     >     >                 }
> >     >     >
> >     >     >                 MappedStatement statement =
> >     >     >     sqlMap.getMappedStatement(mapKey);
> >     >     >                 list =
> >     >     >     statement.executeQueryForList (dbCon.getConnection(),
> >     >     >     param);
> >     >     >             } catch (SQLException e) {
> >     >     >     //            log.error("Error during sql: " +
> >     >     e.getMessage(), e);
> >     >     >
> >     >     >     ExceptionAdaptor.instance
> >     >     (exceptionAdaptorKey).getMappedException(e,
> >     >     >     "getList: " + e.getMessage(), true,
> >     >     >     ExceptionAdaptor.ACTION_SEARCH, param);
> >     >     >             } finally {
> >     >     >                 if (dbCon != null) {
> >     >     >                     dbCon.closeAll
> (handleTransactionLocally);
> >     >     >                 }
> >     >     >             }
> >     >     >             return list;
> >     >     >         }
> >     >     >
> >     >     >
> >     >     >
> >     >     >     I saw a sqlMap.setUserConnection () but this method can
> be
> >     >     called by
> >     >     >     simultaneously so setting the connection in the sqlMap
> >     >     doesn`t make
> >     >     >     sense to me.  There is probably something that I don`t
> >     >     understand!
> >     >     >
> >     >     >     Please help :-)
> >     >     >
> >     >     >     Best Regards.
> >     >     >     /David
> >     >     >     P.S.: Also I do need to get the SQL string from a map to
> >     >     play with it
> >     >     >     myself.  I'm not there yet but I hope there a way to get
> >     >     it!  Thanks
> >     >     >
> >     >     >
> >     >     >
> >     >     >
> >     >     >
> >     >
> >     >
> >     >
> >
> >
> >
>
>
>

Mime
View raw message