ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kevin Schraith" <kev...@dbo2.com>
Subject RE: Slow stored procedure performance?
Date Fri, 11 Feb 2005 15:40:13 GMT
Did you mean to say a single execution within the VM, in case performance is
an issue?  Now when I run it three times, it goes from:

 75s -> 72s -> 65s

Versus for direct execution:

 6s -> 6s -> 6s

We don't absolutely need to run this in iBATIS as you say, but if we don't,
we're back to cobbling the SQL together in Java, which is a lot less
pleasant than using SQL Maps.

I'll see if I can run this under jProfiler and find out where the time is
going, and will post more if I find anything meaningful.

Thanks,
  Kevin


-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com] 
Sent: Sunday, February 06, 2005 9:09 AM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Slow stored procedure performance?


Are you only testing a single execution?  If so, please test many...

Cheers,
Clinton


On Fri, 4 Feb 2005 10:46:12 -0800, Kevin Schraith <kevins@dbo2.com> wrote:
> We're running into some very strange behavior in our implementation 
> using SQL Maps withing the DAO Framework against SQL Server 2000.  
> When we call a particular stored procedure from a query tool, or 
> through plain old JDBC (using jTDS 0.9.1 as our driver), it takes 
> about 4 seconds to run and iterate over the results.  When we put the 
> same thing within a SQL Map, it takes 22 seconds.  Same parameters, 
> same load on DB, everything.  Very reproducable.  Does anyone have any 
> tips on what might be happening?
> 
> The stored procedure in question does a good deal of server side 
> processing, but only returns 50 rows, with a dozen or so columns.  Not 
> much data to iterate.  When looking at the DB profiler tool, it shows 
> the procedure call taking up all of the time, though that time 
> probably includes the time to iterate over the rows of the result set.  
> All I can guess is that something about the results being mapped to to 
> the result class (a simple bean class) is either wildly inefficient or 
> blocking somehow...
> 
> We have been using iBATIS for a while now (though not much with stored 
> procedures), and have never seen any noticable performance overhead.  
> This is a pretty critical blocker for us - any help would be immensely 
> appreciated.
> 
> Thanks in advance,
>  Kevin
> 
> ------------------------
> 
> Here's the map:
> 
> <sqlMap namespace="SafetyUtilizationReport">
> 
>   <typeAlias alias="safetyUtilizationReportCriteria"
> type="com.dbo2.pipeline.model.safety.reports.SafetyUtilizationReportCr
> iteria
> DO"/>
>   <typeAlias alias="safetyUtilizationReportListRow"
>
type="com.dbo2.pipeline.model.safety.reports.SafetyUtilizationReportListRowD
> O"/>
> 
>   <sql id="whereClause">
>     <isNotEmpty prepend="AND" property="contactIDList">
>       SafetyInspection.inspectedByID IN
>       <iterate open="(" property="contactIDList" conjunction=","
close=")">
>         $contactIDList[]$
>       </iterate>
>     </isNotEmpty>
>     <isNotEmpty prepend="AND" property="companyIDList">
>       Contact.zoneID IN (SELECT id FROM Zone WHERE companyID IN
>         <iterate open="(" property="companyIDList" conjunction="," 
> close=")">
>           $companyIDList[]$
>         </iterate>
>       )
>     </isNotEmpty>
>     <isEqual prepend="AND" property="zoneID" compareValue="10003">
>       <!-- Within zone 10003 - potentially see all zones, but discard 
> bogus ones -->
>       Contact.zoneID NOT IN (SELECT id FROM Zone WHERE 
> includeInBenchmarkFlag != 1)
>     </isEqual>
>     <isNotEqual prepend="AND" property="zoneID" compareValue="10003">
>       <!-- Outside of zone 10003 - show only current zone -->
> 
>       <!-- NOTE: because this might get pulled into a dynamic SQL 
> statement in the SP,
>             we cheat a little and don't replace as a param (and no 
> quotes!)
> -->
>       Contact.zoneID = $zoneID$
>     </isNotEqual>
>   </sql>
> 
>   <procedure id="SafetyUtilizationReport.getResults"
>              parameterClass="safetyUtilizationReportCriteria"
>              resultClass="safetyUtilizationReportListRow">
>     <!-- NOTE: because the included chunks may have single quotes, we 
> surround them
>             in double quotes -->
>     { call dbo2sp_SafetyUtilizationReport_runReport (
>               #showDelta#,
>               #topCountSQL#,
>               '<include refid="whereClause"/>',
>               '<include refid="whereClause"/>
>                <isNotEmpty prepend="AND" property="computedBeginDate">
>                  SafetyInspection.inspectionDate &gt;= 
> ''$computedBeginDate$''
>                </isNotEmpty>
>                <isNotEmpty prepend="AND" property="computedEndDate">
>                  SafetyInspection.inspectionDate &lt;=
''$computedEndDate$''
>                </isNotEmpty>',
>                '$orderByClauseSQL$' ) }
>   </procedure>
> 
> </sqlMap>
> 
>



Mime
View raw message