ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kevin Schraith" <kev...@dbo2.com>
Subject Slow stored procedure performance?
Date Fri, 04 Feb 2005 18:46:12 GMT
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