You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Kevin Schraith <ke...@dbo2.com> on 2005/02/04 19:46:12 UTC
Slow stored procedure performance?
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>
OT: Re: Slow stored procedure performance?
Posted by friendVU admin <ma...@friendvu.com>.
Did you heard the one about a cast?
TabbSkelCont cont=(TabbSkelCont) JndiHelper.lookup("board_cont");
Groovy man,. :-P
.V
Clinton Begin wrote:
>
>Man, you're like a self-deprecating comic. First you admit to using
>Maps for your domain model, ..
>;-)
>
>
>
Re: Slow stored procedure performance?
Posted by Clinton Begin <cl...@gmail.com>.
> (ps: one time I wrote a 94 page stored procedure.... and it was sub
> second. I did not use iBatis at the time)
Man, you're like a self-deprecating comic. First you admit to using
Maps for your domain model, now you're claiming responsibility for a
94 page stored proc!!!
;-)
On Sun, 06 Feb 2005 11:40:40 -0600, Vic Cekvenich <vi...@friendvu.com> wrote:
> Clinton Begin wrote:
>
> >> Same parameters, same load on DB, everything. Very
> >>reproducable. Does anyone have any tips on what might be happening?
> >>
> >>
> Can you do even more in the sored procedure itself... and let SQL map
> deal with the mapping of results only?
> Also... sometimes "with recompile" in sql will help pick a good
> execution path.
>
> All guesses....
>
> .V
> (ps: one time I wrote a 94 page stored procedure.... and it was sub
> second. I did not use iBatis at the time)
>
> --
> Forums, Boards, Blogs and News in RiA <http://www.boardVU.com>
>
Re: Slow stored procedure performance?
Posted by Vic Cekvenich <vi...@friendvu.com>.
Clinton Begin wrote:
>> Same parameters, same load on DB, everything. Very
>>reproducable. Does anyone have any tips on what might be happening?
>>
>>
Can you do even more in the sored procedure itself... and let SQL map
deal with the mapping of results only?
Also... sometimes "with recompile" in sql will help pick a good
execution path.
All guesses....
.V
(ps: one time I wrote a 94 page stored procedure.... and it was sub
second. I did not use iBatis at the time)
--
Forums, Boards, Blogs and News in RiA <http://www.boardVU.com>
RE: Slow stored procedure performance?
Posted by Kevin Schraith <ke...@dbo2.com>.
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 <ke...@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.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>
>
>
Re: Slow stored procedure performance?
Posted by Clinton Begin <cl...@gmail.com>.
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 <ke...@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 >=
> ''$computedBeginDate$''
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="computedEndDate">
> SafetyInspection.inspectionDate <= ''$computedEndDate$''
> </isNotEmpty>',
> '$orderByClauseSQL$' ) }
> </procedure>
>
> </sqlMap>
>
>
Re: Slow stored procedure performance?
Posted by Clinton Begin <cl...@gmail.com>.
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 <ke...@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 >=
> ''$computedBeginDate$''
> </isNotEmpty>
> <isNotEmpty prepend="AND" property="computedEndDate">
> SafetyInspection.inspectionDate <= ''$computedEndDate$''
> </isNotEmpty>',
> '$orderByClauseSQL$' ) }
> </procedure>
>
> </sqlMap>
>
>