Re: Simple Query using OJB

Hi Jay,

the object you request (WcpusesVO) has no references and the query is a 
simple lookup by PK. I don't think that the long response time was 
caused by OJB.

Does the WCPUSES table contains many rows (millions?)? If possible 
optimize the table.

Could it be a concurrency issue and another thread/client locks the table?

Could it be a JVM garbage collector issue (the GC runs while the query 
is executed)?

If you are using OJB 1.0.4 you can optimize "lookup by PK" using the 
IdentityFactory and PB.getObjectByIdentity
http://db.apache.org/ojb/docu/tutorials/pb-tutorial.html#Find+object+by+primary+key

regards,
Armin

Xu, Jie (MED US) wrote:
> 
> It happens just sometimes (not always) that connection takes much longer
> to process and the connection is in use for longer than 16 seconds. The
> query looks like
> 
> Select A0.xxx .... From WCPUSES where xxx = nnnnn
> 
> 
> 
> The findByOK is,
> 	public ValueObject findByPK(String primaryKey)
> 
> 		throws DataAccessException {
>       
> 
> 		PersistenceBroker broker = null;
> 		WcpusesVO theVO = null;
>        
> 
> 		try {
> 			broker =
> ServiceLocator.getInstance().findBroker();
> 			theVO = new WcpusesVO();
>             	theVO.setSessionNo(new Integer(primaryKey));
>            
> 
> 			Query query = new QueryByCriteria(theVO);
> 			theVO = (WcpusesVO)
> broker.getObjectByQuery(query);
> 		} catch (ServiceLocatorException e) {
> 			Ms4Logger.log.error("ServiceLocatorException
> thrown in WcpusesDAO.findByPK(): " + e.toString());
> 			throw new
> DataAccessException("ServiceLocatorException thrown in
> WcpusesDAO.findByPK(): " + e.toString(),e);
> 		} finally {
> 			if (broker != null) {
> 				broker.close();
> 			}
> 		}
>        
> 
> 		return theVO;
>     }
> 
> Here is the data mapping
> 
> 	<class-descriptor
> 
> 		class="com.siemens.med.hs.ms4.wcbin.WcpusesVO"
> 
> 		table="WCPUSES">
> 
> 		<field-descriptor
> 
> 			id="1"
> 
> 			name="sessionNo"
> 
> 			column="WCUSID"
> 
> 			jdbc-type="INTEGER" 
> 
> 			primarykey="true"
> 
> 			autoincrement="true"
> 		/>
> 		<field-descriptor
> 
> 			id="2"
> 
> 			name="userId"
> 
> 			column="WCUSER"
> 
> 			jdbc-type="CHAR"
> 
> 
> conversion="com.siemens.med.hs.ms4.dbcommon.conversions.TrimStringsField
> Conversion"
> 		/>
> 		<field-descriptor
> 
> 			id="3"
> 
> 			name="userKey"
> 
> 			column="WCXXXX"
> 
> 			jdbc-type="CHAR"
> 
> 
> conversion="com.siemens.med.hs.ms4.dbcommon.conversions.TrimStringsField
> Conversion"
> 		/>
> 		<field-descriptor
> 
> 			id="4"
> 
> 			name="remoteIp"
> 
> 			column="WCRMTX"
> 
> 			jdbc-type="CHAR"
> 
> 
> conversion="com.siemens.med.hs.ms4.dbcommon.conversions.TrimStringsField
> Conversion"
> 		/>
> 		<field-descriptor
> 
> 			id="5"
> 
> 			name="updateDate"
> 
> 			column="WCUDAT"
> 
> 			jdbc-type="INTEGER" 
> 
> 		/>
> 		<field-descriptor
> 
> 			id="6"
> 
> 			name="updateTime"
> 
> 			column="WCUTIM"
> 
> 			jdbc-type="INTEGER" 
> 
> 		/>
> 		<field-descriptor
> 
> 			id="7"
> 
> 			name="accessDate"
> 
> 			column="WCLDAT"
> 
> 			jdbc-type="INTEGER" 
> 
> 		/>
> 		<field-descriptor
> 
> 			id="8"
> 
> 			name="accessTime"
> 
> 			column="WCLTIM"
> 
> 			jdbc-type="INTEGER" 
> 
> 		/>
> 		<field-descriptor
> 
> 			id="9"
> 
> 			name="gsmSessionId"
> 
> 			column="WCGSID"
> 
> 			jdbc-type="CHAR"
> 
> 
> conversion="com.siemens.med.hs.ms4.dbcommon.conversions.TrimStringsField
> Conversion"
> 		/>
> 		<field-descriptor
> 
> 			id="10"
> 
> 			name="gsmTimeout"
> 
> 			column="WCGTOT"
> 
> 			jdbc-type="INTEGER" 
> 
> 		/>	
> 
> 		<field-descriptor
> 
> 			id="11"
> 
> 			name="workstationId"
> 
> 			column="WCWSID"
> 
> 			jdbc-type="CHAR"
> 
> 
> conversion="com.siemens.med.hs.ms4.dbcommon.conversions.TrimStringsField
> Conversion"
> 		/>
> 
> 	
> 
> 	</class-descriptor>
> 
> 
> 
> 
> "Jay" Jie Xu
> Siemens MED
> 
> -----Original Message-----
> From: Armin Waibel [mailto:[email protected]]
> 
> Sent: Thursday, January 04, 2007 9:14 PM
> To: OJB Users List
> Subject: Re: Simple Query using OJB
> 
> Hi Jay,
> 
> Xu, Jie (MED US) wrote:
>> Hello,
>>
> 
> 
>> We are using OJB in our Web application and we turn on the trace to
>> monitor the DataSource connections. We find that when we are running a
>> simple query through OJB, some time the connection will be in use for
>> quite long time (say 16 seconds). Any insight on this?
>>
> 
> 
> Could you please post the query and the mapping of the queried object?
> Do you lookup a PB instance, perform the query and close the used PB
> 
> instance immediately after use?
> 
> regards,
> Armin
> 
> 
>> Here is the trace
>>
> 
> 
> 
> 
>> MCWrapper id 89846770  Managed connection
>> com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@5e23cf97
>> State:STATE_TRAN_WRAPPER_INUSE Thread Id: 14f5fdea Thread Name:
>> Servlet.Engine.Transports : 685 Handle count 0
>>      Start time inuse Thu Jan 04 08:07:42 CST 2007 Time inuse 16
>> (seconds)
>>      Last allocation time Thu Jan 04 08:07:42 CST 2007
>>        getConnection stack trace information:
>>           t java.lang.Throwable.<init>(Throwable.java:179)
>>  at
>>
> com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.j
>> ava:435)
>>  at
>>
> com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSourc
>> e.java:235)
>>  at
>>
> com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSourc
>> e.java:205)
>>  at
>>
> org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl.newConne
>> ctionFromDataSource(Unknown Source)
>>  at
>>
> org.apache.ojb.broker.accesslayer.ConnectionFactoryAbstractImpl.lookupCo
>> nnection(Unknown Source)
>>  at
>>
> org.apache.ojb.broker.accesslayer.ConnectionManagerImpl.getConnection(Un
>> known Source)
>>  at
>>
> org.apache.ojb.broker.accesslayer.StatementManager.getPreparedStatement(
>> Unknown Source)
>>  at
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(Unknown
>> Source)
>>  at
> org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(Unknown
>> Source)
>>  at org.apache.ojb.broker.accesslayer.RsIterator.<init>(Unknown
> Source)
>>  at
>>
> org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(Unknow
>> n Source)
>>  at
>>
> org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(
>> Unknown Source)
>>  at
>>
> org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(Un
>> known Source)
>>  at
>>
> org.apache.ojb.broker.core.PersistenceBrokerImpl.getObjectByQuery(Unknow
>> n Source)
>>  at
>>
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getObjectByQuery(
>> Unknown Source)
>>  at
>>
> org.apache.ojb.broker.core.DelegatingPersistenceBroker.getObjectByQuery(
>> Unknown Source)
>>  at com.siemens.apps.wcbin.dao.WcpusesDAO.findByPK(WcpusesDAO.java:50)
>>  at
>> com.siemens.apps.filters.SessionFilter.doFilter(SessionFilter.java:69)
>>  at
>>
> com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInst
>> anceWrapper.java:130)
>>  at
>>
> com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterCh
>> ain.java:54)
>>  at
>>
> com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispa
>> tch(WebAppRequestDispatcher.java:807)
>>  at
>>
> com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRe
>> questDispatcher.java:269)
>>  at
>>
> com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppReq
>> uestDispatcher.java:156)
>>  at
>>
> com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:1
>> 21)
>>  at
>>
> com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInv
>> oker.java:229)
>>  at
>>
> com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocati
>> on(CachedInvocation.java:66)
>>  at
>>
> com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invo
>> ke(CacheableInvocationContext.java:106)
>>  at
>>
> com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(Servle
>> tRequestProcessor.java:126)
>>  at
>>
> com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSELis
>> tener.java:317)
>>  at
>>
> com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection
>> .java:56)
>>  at
>>
> com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:
>> 576)
>>  at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:376)
>>  at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:915)
>>
> 
> 
> 
> 
> 
>> "Jay" Jie Xu
>> Siemens MED
>>
> 
> 
> 
> 
> ------------------------------------------------------------------------
> ----
>> This message and any included attachments are from Siemens Medical
> Solutions
> 
>> and are intended only for the addressee(s).
>>
> 
>> The information contained herein may include trade secrets or
> privileged or
> 
>> otherwise confidential information. Unauthorized review, forwarding,
> printing,
> 
>> copying, distributing, or using such information is strictly
> prohibited and may
> 
>> be unlawful. If you received this message in error, or have reason to
> believe
> 
>> you are not authorized to receive it, please promptly delete this
> message and
> 
>> notify the sender by e-mail with a copy to
> [email protected]
> 
> 
>> Thank you
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
> 
> 
> ----------------------------------------------------------------------------
> This message and any included attachments are from Siemens Medical Solutions
> 
> and are intended only for the addressee(s).
> 
> The information contained herein may include trade secrets or privileged or
> 
> otherwise confidential information. Unauthorized review, forwarding, printing,
> 
> copying, distributing, or using such information is strictly prohibited and may
> 
> be unlawful. If you received this message in error, or have reason to believe
> 
> you are not authorized to receive it, please promptly delete this message and
> 
> notify the sender by e-mail with a copy to [email protected]
> 
> 
> Thank you
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
> 
>