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:[EMAIL PROTECTED] Sent: Sunday, February 06, 2005 9:09 AM To: [email protected] 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 <[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.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 >= > ''$computedBeginDate$'' > </isNotEmpty> > <isNotEmpty prepend="AND" property="computedEndDate"> > SafetyInspection.inspectionDate <= ''$computedEndDate$'' > </isNotEmpty>', > '$orderByClauseSQL$' ) } > </procedure> > > </sqlMap> > >
