ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rao, Satish" <satish....@fmr.com>
Subject RE: Performance issue with CustomTypeHandler
Date Tue, 18 Oct 2005 21:07:17 GMT
Hi Mike,
 
Thanks for the prompt response.
 
If we run the PL/SQL via SQL Plus, we don't experience the same delay
(there are about a million records in the database)
As far as the database is concerned, we don't have much insight into the
configuration. Can you please provide me a list of possible database
parameters that we need to take a look at?
 
Please note that even though there are a few thousand rows we always
fetch only 10 records. In other words, the cursor should have only 10
records at any point in time.
 
I debugged the code and here's the behavior I see. Do you see a similar
pattern?
1. Procedure executes
2. control gets transferred to RefCursorHandler (for OUT parameter of
ORACLECURSOR jdbcType). The following statement gets called          
                this.result = (ResultSet)arg0.getObject();
3. next, ResultGetterImpl getObject() gets called. The following
statement within that method gets executed
                return rs.getObject(index); 
4. next, CallableStatementResultSet getObject(int columnIndex) gets
called.
5. next, oracle.jdbc.driver.OracleCallableStatement gets called (At this
point it takes a long time) and I can see NetSocket statements. 
Does it mean the statement is making a call to the database again?
Assuming it is fetching data from the cursor, there should be only 10
records in the cursor and it shouldn't be taking that long. Do you think
it is looping thru thousands of records again?
 
Thanks,
Satish
 
 

	-----Original Message-----
	From: Mike Fagan [mailto:mfagan@tde.com] 
	Sent: Tuesday, October 18, 2005 4:40 PM
	To: user-java@ibatis.apache.org
	Subject: Re: Performance issue with CustomTypeHandler
	
	
	Satish,
	
	I am also using oracle ref cursors and do not see the delay you
are experiencing.
	
	Has something possibly changed in your database and the delay
you see is really in the pl/sql?
	
	Regards,
	Mike Fagan
	
	
	Rao, Satish wrote: 

		Is anyone facing a similar issue. Please suggest.

			-----Original Message-----
			From: Rao, Satish 
			Sent: Tuesday, October 18, 2005 1:44 PM
			To: user-java@ibatis.apache.org
			Subject: Performance issue with
CustomTypeHandler
			
			


			I am using ORACLECURSOR in my sqlmap and using
the following TypeHandler code. 

			Here's the sqlmap entry 
			                <parameter property="offerRS"
jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT"
typeHandler="RefCursorHandler"/>

			And here's the handler code 
			public class RefCursorHandler implements
TypeHandlerCallback { 

			        public Object getResult(ResultGetter
arg0) throws SQLException { 

			                this.result =
(ResultSet)arg0.getObject(); 
			                return this.result; 
			        } 

			The statement (ResultSet)arg0.getObject(); takes
a long time to return (even for 2 rows). 

			Do you see any issue with the code? 
			I debugged this and eventually found that the
above statement calls the following method within
CallableStatementResultSet

			  public Object getObject(int columnIndex)
throws SQLException { 
			    return cs.getObject(columnIndex); 
			  } 

			This in turn calls OracleCallableStatement. This
part takes a long time to return. 

			I am using thin driver. 

			Please suggest. I need to get this resolved
ASAP. 

			Thanks, 
			Satish 



Mime
View raw message