Having looked at this again, I actually wonder if iBATIS is doing much
in the way of making this any cleaner. That is a seriously insane
stored proc. Although I can't believe there would be an 18 second
difference between the two (it uses all the same code as normal
statements), I will say that if it turns out to be the case -- just
use JDBC here..
I'm personally impressed that you were actually able to map something
so crazy! :-)
Cheers,
Clinton
On Fri, 4 Feb 2005 10:46:12 -0800, Kevin Schraith <[EMAIL PROTECTED]> 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 >=
> ''$computedBeginDate$''
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="computedEndDate">
> SafetyInspection.inspectionDate <= ''$computedEndDate$''
> </isNotEmpty>',
> '$orderByClauseSQL$' ) }
> </procedure>
>
> </sqlMap>
>
>
