ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Fagan <mfa...@tde.com>
Subject Re: Performance issue with CustomTypeHandler
Date Wed, 19 Oct 2005 15:48:51 GMT
Rao, Satish wrote:

> 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
>>
>
Yes, It is talking to oracle and I beleive it is now actually executing 
the cursor reference it returned to you.
If you are using the thin driver make sure you have the latest version.
I am puzzed why is runs slower in JDBC than SQL Plus, unless there are 
some user or complex oracle object defined in the cursor or character 
conversion is happening across the wire.

Regards,
Mike Fagan



Mime
View raw message