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 Tue, 15 Feb 2005 09:35:49 GMT
Upon further investigation, it looks like the difference here is the fact
that iBATIS is using a PreparedStatement, whereas my pure JDBC approach was
just using a regular statement.  Again, this is being done using jTDS as the
JDBC driver - does anyone have a guess as to why this would incur such
overhead?  

For clearer information, I've found that the numbers from earlier in this
posting thread were off, should be more like:

For prepared statements (iBATIS or direct JDBC):
  22s -> 20s -> 21s
 
For non-prepared statements:
  6s -> 6s -> 6s

I've seen some mentions in various forums that for low usage counts of
Prepared Statements, in fact simple Statements will execute (somewhat)
faster.  It may be that this is the only difference.  I could find no way to
avoid using prepared statements in iBATIS - is that correct?  

Kevin

-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com] 
Sent: Friday, February 11, 2005 9:25 AM
To: Kevin Schraith
Subject: Re: Slow stored procedure performance?


That would be great Kevin.

In the mean time, could you send me the tests your using?  (I don't need the
proc, I just want to see the tests and both implementations).

Cheers,
Clinton




On Fri, 11 Feb 2005 07:40:13 -0800, Kevin Schraith <kevins@dbo2.com> wrote:
> 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.SafetyUtilizationReport
> > Cr
> > iteria
> > DO"/>
> >   <typeAlias alias="safetyUtilizationReportListRow"
> >
> type="com.dbo2.pipeline.model.safety.reports.SafetyUtilizationReportLi
> stRowD
> > 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