ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Gagnon <dgag...@siunik.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 20:24:59 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)
Yes but you know the problem is that there more than 60 existing reports 
.. so a bunch of work/test in perspective.  Anyway I will look into it.  
Maybe it's not that complicated...  :-)

I'll keep you informed. 

Thanks for your help!
/David


>
> Cheers,
> Clinton
>
> On 8/17/06, *David Gagnon* < dgagnon@siunik.com 
> <mailto: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>
>     > <mailto: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>>
>     >     > <mailto: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>>>
>     >     >     > <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 <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