ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jan Vissers <Jan.Viss...@cumquat.nl>
Subject Re: why does SqlMap ibatis is slower that JdbcDaoTemplate
Date Mon, 10 Oct 2005 21:51:31 GMT
As I replied earlier in this thread....


"I also checked with the single point of truth - the database. For those 
interested...

Mapping:

     <select id="getRegisteredAction" resultClass="registeredAction">
        SELECT REAC_ID as id
        ,      REAC_NAME as name
        ,      REAC_DESCRIPTION as description
        ,      REAC_IND_ACTIVE as active
        FROM   CXF_REGISTERED_ACTIONS
        WHERE  REAC_ID = #value#
     </select>

Mini tester:

     public static void main(String[] args) throws SQLException {
       SqlMapClient sqlMap = SqlMapConfig.getSqlMapInstance();
       PreparedStatement stmt = sqlMap.getCurrentConnection().
         prepareStatement("alter system set sql_trace=true");
       stmt.execute();
    
       sqlMap.queryForObject ("getRegisteredAction", new Long(1));      
       sqlMap.queryForObject ("getRegisteredAction", new Long(2));
     }

Execute the above on a freshly started database instance.

Interpretation of Oracle trace file:

1st occurrence:

PARSING IN CURSOR #3 len=190 dep=0 uid=57 oct=3 lid=57 tim=2640378680 
hv=2472912027 ad='69e65bc8'
SELECT REAC_ID as id     ,      REAC_NAME as name     ,      
REAC_DESCRIPTION as description     ,      REAC_IND_ACTIVE as active     
FROM   CXF_REGISTERED_ACTIONS     WHERE  REAC_ID = *:1*  END OF STMT
PARSE #3:c=0,*e=353*,p=0,cr=0,cu=0,*mis=1*,r=0,dep=0,og=1,tim=2640378675

2nd occurrence:

PARSING IN CURSOR #4 len=190 dep=0 uid=57 oct=3 lid=57 tim=2640557676 
hv=2472912027 ad='69e65bc8'
SELECT REAC_ID as id     ,      REAC_NAME as name     ,      
REAC_DESCRIPTION as description     ,      REAC_IND_ACTIVE as active     
FROM   CXF_REGISTERED_ACTIONS     WHERE  REAC_ID = :1  END OF STMT
PARSE #4:c=0,*e=59*,p=0,cr=0,cu=0,*mis=0*,r=0,dep=0,og=1,tim=2640557673

Notice that:

  1. Bind variables are actually used   *:1*
  2. 1st occurence is an Oracle hard parse indicated by a library cache 
mis  *mis=1*
  3. 2nd occurence is an Oracle soft parse indicated by a library cache 
hit  *mis=0*
  4. Hard parses are (obviously) more expensive than soft, *353 vs. 59*

"

Clinton Begin wrote:

> Ooops.....Sven, that is incorrect.
>
> Whether you use #inline# parameters or explicit parameter markers (?), 
> iBATIS will always use prepared statements with parameter markers.  
> iBATIS NEVER uses hardcoded arguments unless you use string 
> substitution using the $ syntax. 
>
> Cheers,
> Clinton
>
> On 10/10/05, *Castillo, Jose Rodrigo (LanInfo)* 
> <jcastillo@lancargo.com <mailto:jcastillo@lancargo.com>> wrote:
>
>     Hi Sven:
>
>     How can i use paramaters markets with ibatis?
>
>     Thank you very much for your good explication!!!
>
>     Regards,
>     Rodrigo
>
>     "Sven Boden" <list123@pandora.be <mailto:list123@pandora.be>>
>     wrote in message
>     news:<h47bk1hem9t6irpgc4rq8ipph6h1893t2t@4ax.com
>     <mailto:h47bk1hem9t6irpgc4rq8ipph6h1893t2t@4ax.com>>...
>
>     Simple... the SQL code executed by the JDBC example and the iBATIS
>     version is not the same. The JDBC examples use parameter markers and
>     proper arguments, while the iBATIS version in the original mail uses
>     inline parameters, the #value# (which means that the SQL generated
>     will contain hardcoded parameters)... By the way iBATIS can also use
>     parameters markers.
>
>     Oracle performance suffers very badly when using hardcoded arguments.
>     I could make my own example but have a look e.g. at Tom Kyte's site
>     http://asktom.oracle.com and search for "hard parsing"... you will get
>     a complete explanation why hardcoding is bad.
>
>     Personally I've seen in Oracle examples of executing SQL in a loop
>     with different arguments where with proper parameters the code would
>     take 2 seconds, and with harcoded arguments the "same" SQL took 2
>     minutes.
>
>     So if the database is Oracle a more proper comparison would be to
>     hardcode the arguments in the JDBC example, or use parameter markers
>     in iBATIS.
>
>     Regards,
>     Sven
>
>
>     On Thu, 06 Oct 2005 23:45:41 +0200, you wrote:
>
>     >Hi Sven,
>     >
>     >Like to hear from you, how you came to that conclusion.
>     >
>     >-J
>     >
>
>
>     ----------
>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.344 / Virus Database: 267.11.10/120 - Release Date: 10/5/2005
>  
>

-- 
Cumquat Information Technology
De Dreef 19
3706 BR Zeist
T +31 (0)30 - 6940490
F +31 (0)10 - 6940499
http://www.cumquat.nl

Jan.Vissers@cumquat.nl
M +31 6 51 169 556



Mime
View raw message