ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Clinton Begin <clinton.be...@gmail.com>
Subject Re: Slow stored procedure performance?
Date Sun, 06 Feb 2005 17:08:35 GMT
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.SafetyUtilizationReportCriteria
> 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