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 "Barnett, Brian W." <br...@pearson.com> on 2005/05/24 00:04:52 UTC
RE: Query performs differently in SQL Query Analyzer [SOLVED]
I decided to attempt to eliminate the items that Clinton had mentioned were
outside the iBATIS framework (network, JDBC, driver). I started by swapping
out the Microsoft JDBC driver with the jTDS JDBC driver for SQL Server
(http://jtds.sourceforge.net/ <http://jtds.sourceforge.net/> ) and that
fixed the performance problem with this particular query... woo hooooo!!!
We have yet to do complete testing throughout the rest of the application,
but we are hopeful that all other queries will perform at least as well as
they did with the Microsoft driver.
Thank you all for your input.
Brian Barnett
-----Original Message-----
From: Barnett, Brian W.
Sent: Monday, May 23, 2005 2:19 PM
To: 'ibatis-user-java@incubator.apache.org'
Subject: RE: Query performs differently in SQL Query Analyzer
>>where and when do you build your SqlMapClient instance?
We used JPetStore 4 as a model for using iBATIS. We have a DaoManager
singleton that gets created during app startup. dao.xml and
sql-map-config.xml files are parsed and the DaoManager caches instances of
our daos.
>>how often are you connecting?
Not sure how to answer this.
>>which datasource are you using?
Snippet from sql-map-config
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
useStatementNamespaces="false"
maxSessions="128"
maxTransactions="32"
maxRequests="512"/>
<transactionManager type="JDBC">
<dataSource type="JNDI">
<property name="DataSource" value="java:comp/env/bppool"/>
</dataSource>
</transactionManager>
>>are you using lazy loading?
Yes
>>join fetching?
Don't know
>> complex column mapping via select="" (N+1 selects problem)?
No
Here is some additional information:
1. We run many, many queries through our web app and none of the others
exhibit this behavior.
2. The performance problem is not seen on smaller databases. (smaller number
of rows in tables.)
3. The following line of code takes 55 seconds to execute
a. return executeQueryForList("getStudentProfileDataCombined", paramMap);
b. Where paramMap has one entry called "sql" which contains the SQL
statement.
4. Subsequent calls to the above line of code for the same student come back
in 1 second.
5. We're running 2.0.0 RC5 Build 274.
-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: Monday, May 23, 2005 12:30 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Query performs differently in SQL Query Analyzer
>>Each time it encounters a new query?
Let's define new query:
SELECT * FROM STUDENT WHERE STUDENT_ID = #id#
That's one query, regardless of what parameter you use for ?. In iBATIS,
this is represented as:
But if you have any dynamic parts to the SQL or any $substitutions$, then
you are potentially creating a new query.
Regardless, there is NOTHING iBATIS does that should take a minute or
longer, especially after initial runs.
You'll need to provide us with a lot more detail and do some of your own
profiling to test out your situation properly.
First of all, DO NOT compare iBATIS with queries run from Query
Analyzer....that's ridiculous. You've eliminated more than iBATIS in that
scenario, you've also eliminated the network, JDBC, the driver and all of
your own Java code.
My first questions would be: where and when do you build your SqlMapClient
instance? how often are you connecting? which datasource are you using?
do you have any dynamic SQL? are you using maps or beans? are you using
lazy loading, join fetching, complex colum mapping via select="" (N+1
selects problem)?
Performance problems are hard to diagnose properly. Can you give us any
more information?
Clinton
On 5/23/05, Barnett, Brian W. <brian.barnett@pearson.com
<ma...@pearson.com> > wrote:
When you say iBatis is doing way more than just running SQL, when does it do
*way more*? Each time it encounters a new query? Just the first query it
processes after startup? Is the *way more* documented somewhere?
The SQL I run in Query Analyzer is exactly the same. I pulled it from the
logging, which I turned off after your suggestion, but did not see a
noticeable difference.
-----Original Message-----
From: Nathan Maves [mailto: Nathan.Maves@Sun.COM
<ma...@Sun.COM> ]
Sent: Monday, May 23, 2005 11:19 AM
To: ibatis-user-java@incubator.apache.org
<ma...@incubator.apache.org>
Subject: Re: Query performs differently in SQL Query Analyzer
This truly sound like a sql problem. Make sure that the sql that is
generated by ibatis is what you run in your analyzer.
On a side note make sure that all logging is turned off for your
application. This is a huge performance hit if it is turned on for
any part of your app.
Nathan
On May 23, 2005, at 10:53 AM, Barnett, Brian W. wrote:
> Yes, I did notice that subsequent times the exact same query performed
> better. The problem is that in our app, a teacher is presented with
> a list
> of students in her class. She wants to run a report on each one.
> Clicking on
> a student executes the query below but with a different student id.
>
> It takes over a minute each time she selects a new student. Is this
> because
> the query is not identical each time? It really does me no good that
> subsequent times the query runs better, because the teacher doesn't
> need to
> run the same student more than once.
>
> Suggestions?
>
> -----Original Message-----
> From: Nathan Maves [mailto:Nathan.Maves@Sun.COM
<ma...@Sun.COM> ]
> Sent: Monday, May 23, 2005 10:59 AM
> To: ibatis-user-java@incubator.apache.org
<ma...@incubator.apache.org>
> Subject: Re: Query performs differently in SQL Query Analyzer
>
>
> I think this needs a FAQ :)
>
> This has been cover quite a few times.
>
> iBatis is doing way more then just running sql. Make sure that to
> truly test the performance of iBatis that you run the query multiple
> times. The initial run is creating many object and sets everything
> up. Check the speed of the second and third runs to see more accurate
> results.
>
> Nathan
>
> On May 23, 2005, at 10:13 AM, Barnett, Brian W. wrote:
>
>
>> I have a query that returns in 3 or 4 seconds when I run it in
>> Microsoft SQL Query Analyzer but takes over a minute to return when
>> run through iBATIS.
>> Can anyone give me some clues as to what I should check?
>>
>> Here is the SQL Map stuff:
>>
>> <resultMap id="student_profile_combined_result"
>> class="java.util.HashMap">
>> <result property="abbrev" column="abbrev"
>> nullValue="null_string"/>
>> <result property="session" column="session_name"
>> nullValue="null_string"/>
>> <result property="display" column="display"
>> nullValue="null_string"/>
>> <result property="dateTested" column="dateTested"
>> nullValue="null_string"/>
>> <result property="proficiencyLevel"
>> column="proficiency_level"
>> nullValue="null_string"/>
>> <result property="theTestScore" column="the_test_score"
>> nullValue="-999"/>
>> <result property="schoolScoreAvg" column="schoolScoreAvg"
>> nullValue="-999"/>
>> <result property="distScoreAvg" column="distScoreAvg"
>> nullValue="-999"/>
>> <result property="gradeLevelId" column="grade_level_id"
>> nullValue="-999"/>
>> <result property="schoolName" column="school_name"
>> nullValue="null_string"/>
>> <result property="assessmentId" column="assessment_id"
>> nullValue="-999"/>
>> <result property="schoolRawAvg" column="schoolRawAvg"
>> nullValue="-999"/>
>> <result property="distRawAvg" column="distRawAvg"
>> nullValue="-999"/>
>> <result property="lowcut" column="lowcut" nullValue="-999"/>
>> <result property="highcut" column="highcut"
>> nullValue="-999"/>
>> <result property="prof" column="prof" nullValue="-999"/>
>> <result property="ppId" column="pp_id" nullValue="-999"/>
>> <result property="theRawPoints" column="the_raw_points"
>> nullValue="-999"/>
>> <result property="scoreMethodId" column="score_method_id"
>> nullValue="-999"/>
>> <result property="aboveStandard" column="above_standard"
>> nullValue="-999"/>
>> <result property="schoolAboveStandard"
>> column="school_above_standard" nullValue="-999"/>
>> <result property="districtAboveStandard"
>> column="district_above_standard" nullValue="-999"/>
>> <result property="sessionId" column="session_id"
>> nullValue="-999"/>
>> <result property="maxScore" column="maxScore"
>> nullValue="-999"/>
>> <result property="minScore" column="minScore"
>> nullValue="-999"/>
>> </resultMap>
>>
>> <select id="getStudentProfileDataCombined"
>> resultMap="student_profile_combined_result"
>> parameterClass="java.util.HashMap">
>> $sql$
>> </select>
>>
>> Here is the query:
>>
>> SELECT a.abbrev, tsts.session_name , tsts.session_id, y.display,
>> ts.dateTested, ppl.proficiency_level, ts.test_score AS
>> the_test_score,
>> avs.schoolScoreAvg, avd.distScoreAvg, gr.grade_level_id,
>> sc.school_name,
>> a.assessment_id, avs.schoolRawAvg, avs.school_above_standard,
>> avd.distRawAvg, avd.district_above_standard, pp.lowcut, pp.highcut,
>> pp.prof, pp.pp_id, ts.test_raw_points AS the_raw_points,
>> a.score_method_id,
>> ppl.above_standard, avd.maxScore, avd.minScore
>> FROM assessment a, test_score ts, school sc, grade_level gr,
>> d_avg_test_score avd, s_avg_test_score avs,
>> proficiency_profile_levels ppl,
>> view_pp pp, test_session tsts, test t, school_year y
>> WHERE a.assessment_id = ts.assessment_id and ts.sch_student_id =
>> 13120 AND
>> t.session_id = tsts.session_id AND y.year_id = t.year_id AND
>> a.assessment_id = t.assessment_id AND ts.test_id = t.test_id AND
>> avs.assessment_id = ts.assessment_id AND avs.school_id =
>> ts.school_id AND
>> avs.test_id = ts.test_id AND avs.session_id = t.session_id AND
>> avs.year_id
>> = t.year_id AND avs.grade_level_id = ts.grade_level_id AND
>> avd.assessment_id = ts.assessment_id AND avd.district_id =
>> ts.district_id
>> AND avd.grade_level_id = ts.grade_level_id AND
>> avd.test_id = ts.test_id AND avd.session_id = t.session_id AND
>> avd.year_id
>> = t.year_id AND sc.school_id = ts.school_id AND
>> gr.grade_level_id = ts.grade_level_id AND
>> ppl.proficiency_profile_level_id
>> = pp.ppl_id AND pp.a_id = a.assessment_id AND
>> pp.pp_id = a.primary_prof_profile_id AND ts.grade_level_id =
>> pp.gl_id AND
>> pp.rce_id IS NULL AND t.session_id = pp.s_id AND
>> ts.test_score BETWEEN pp.lowcut AND pp.highcut
>> ORDER BY ts.dateTested DESC
>>
>> TIA,
>> Brian Barnett
>>
>> *********************************************************************
>> *
>> ******
>> This email may contain confidential material.
>> If you were not an intended recipient,
>> Please notify the sender and delete all copies.
>> We may monitor email to and from our network.
>> *********************************************************************
>> *
>> ******
>>
>>
>
> **********************************************************************
> ******
> This email may contain confidential material.
> If you were not an intended recipient,
> Please notify the sender and delete all copies.
> We may monitor email to and from our network.
> **********************************************************************
> ******
>
****************************************************************************
This email may contain confidential material.
If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
****************************************************************************
****************************************************************************
This email may contain confidential
material. If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
***************************************************************************
****************************************************************************
This email may contain confidential material.
If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
****************************************************************************
R: Query performs differently in SQL Query Analyzer [SOLVED]
Posted by Fabrizio Gianneschi <fa...@gruppoatlantis.com>.
The Microsoft JDBC driver for SQLServer is ***very*** buggy.
We've got a better experience using jTDS in our projects, too.
Fabrizio
_____
Da: Clinton Begin [mailto:clinton.begin@gmail.com]
Inviato: mercoledì 25 maggio 2005 6.29
A: ibatis-user-java@incubator.apache.org
Oggetto: Re: Query performs differently in SQL Query Analyzer [SOLVED]
Thanks for sharing this Brian.
Keep us posted on anything you learn about this. Even though it's the
driver's problem, we'd be interested if there was anything that we can do
with iBATIS to improve it.
Clinton
On 5/23/05, Barnett, Brian W. <br...@pearson.com> wrote:
I decided to attempt to eliminate the items that Clinton had mentioned were
outside the iBATIS framework (network, JDBC, driver). I started by swapping
out the Microsoft JDBC driver with the jTDS JDBC driver for SQL Server
(http://jtds.sourceforge.net/) and that fixed the performance problem with
this particular query... woo hooooo!!!
We have yet to do complete testing throughout the rest of the application,
but we are hopeful that all other queries will perform at least as well as
they did with the Microsoft driver.
Thank you all for your input.
Brian Barnett
-----Original Message-----
From: Barnett, Brian W.
Sent: Monday, May 23, 2005 2:19 PM
To: 'ibatis-user-java@incubator.apache.org'
Subject: RE: Query performs differently in SQL Query Analyzer
>>where and when do you build your SqlMapClient instance?
We used JPetStore 4 as a model for using iBATIS. We have a DaoManager
singleton that gets created during app startup. dao.xml and
sql-map-config.xml files are parsed and the DaoManager caches instances of
our daos.
>>how often are you connecting?
Not sure how to answer this.
>>which datasource are you using?
Snippet from sql-map-config
<sqlMapConfig>
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
useStatementNamespaces="false"
maxSessions="128"
maxTransactions="32"
maxRequests="512"/>
<transactionManager type="JDBC">
<dataSource type="JNDI">
<property name="DataSource" value="java:comp/env/bppool"/>
</dataSource>
</transactionManager>
>>are you using lazy loading?
Yes
>>join fetching?
Don't know
>> complex column mapping via select="" (N+1 selects problem)?
No
Here is some additional information:
1. We run many, many queries through our web app and none of the others
exhibit this behavior.
2. The performance problem is not seen on smaller databases. (smaller number
of rows in tables.)
3. The following line of code takes 55 seconds to execute
a. return executeQueryForList("getStudentProfileDataCombined", paramMap);
b. Where paramMap has one entry called "sql" which contains the SQL
statement.
4. Subsequent calls to the above line of code for the same student come back
in 1 second.
5. We're running 2.0.0 RC5 Build 274.
-----Original Message-----
From: Clinton Begin [mailto:clinton.begin@gmail.com]
Sent: Monday, May 23, 2005 12:30 PM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Query performs differently in SQL Query Analyzer
>>Each time it encounters a new query?
Let's define new query:
SELECT * FROM STUDENT WHERE STUDENT_ID = #id#
That's one query, regardless of what parameter you use for ?. In iBATIS,
this is represented as:
But if you have any dynamic parts to the SQL or any $substitutions$, then
you are potentially creating a new query.
Regardless, there is NOTHING iBATIS does that should take a minute or
longer, especially after initial runs.
You'll need to provide us with a lot more detail and do some of your own
profiling to test out your situation properly.
First of all, DO NOT compare iBATIS with queries run from Query
Analyzer....that's ridiculous. You've eliminated more than iBATIS in that
scenario, you've also eliminated the network, JDBC, the driver and all of
your own Java code.
My first questions would be: where and when do you build your SqlMapClient
instance? how often are you connecting? which datasource are you using?
do you have any dynamic SQL? are you using maps or beans? are you using
lazy loading, join fetching, complex colum mapping via select="" (N+1
selects problem)?
Performance problems are hard to diagnose properly. Can you give us any
more information?
Clinton
On 5/23/05, Barnett, Brian W. <br...@pearson.com> wrote:
When you say iBatis is doing way more than just running SQL, when does it do
*way more*? Each time it encounters a new query? Just the first query it
processes after startup? Is the *way more* documented somewhere?
The SQL I run in Query Analyzer is exactly the same. I pulled it from the
logging, which I turned off after your suggestion, but did not see a
noticeable difference.
-----Original Message-----
From: Nathan Maves [mailto: Nathan.Maves@Sun.COM]
Sent: Monday, May 23, 2005 11:19 AM
To: ibatis-user-java@incubator.apache.org
Subject: Re: Query performs differently in SQL Query Analyzer
This truly sound like a sql problem. Make sure that the sql that is
generated by ibatis is what you run in your analyzer.
On a side note make sure that all logging is turned off for your
application. This is a huge performance hit if it is turned on for
any part of your app.
Nathan
On May 23, 2005, at 10:53 AM, Barnett, Brian W. wrote:
> Yes, I did notice that subsequent times the exact same query performed
> better. The problem is that in our app, a teacher is presented with
> a list
> of students in her class. She wants to run a report on each one.
> Clicking on
> a student executes the query below but with a different student id.
>
> It takes over a minute each time she selects a new student. Is this
> because
> the query is not identical each time? It really does me no good that
> subsequent times the query runs better, because the teacher doesn't
> need to
> run the same student more than once.
>
> Suggestions?
>
> -----Original Message-----
> From: Nathan Maves [mailto:Nathan.Maves@Sun.COM]
> Sent: Monday, May 23, 2005 10:59 AM
> To: ibatis-user-java@incubator.apache.org
> Subject: Re: Query performs differently in SQL Query Analyzer
>
>
> I think this needs a FAQ :)
>
> This has been cover quite a few times.
>
> iBatis is doing way more then just running sql. Make sure that to
> truly test the performance of iBatis that you run the query multiple
> times. The initial run is creating many object and sets everything
> up. Check the speed of the second and third runs to see more accurate
> results.
>
> Nathan
>
> On May 23, 2005, at 10:13 AM, Barnett, Brian W. wrote:
>
>
>> I have a query that returns in 3 or 4 seconds when I run it in
>> Microsoft SQL Query Analyzer but takes over a minute to return when
>> run through iBATIS.
>> Can anyone give me some clues as to what I should check?
>>
>> Here is the SQL Map stuff:
>>
>> <resultMap id="student_profile_combined_result"
>> class="java.util.HashMap">
>> <result property="abbrev" column="abbrev"
>> nullValue="null_string"/>
>> <result property="session" column="session_name"
>> nullValue="null_string"/>
>> <result property="display" column="display"
>> nullValue="null_string"/>
>> <result property="dateTested" column="dateTested"
>> nullValue="null_string"/>
>> <result property="proficiencyLevel"
>> column="proficiency_level"
>> nullValue="null_string"/>
>> <result property="theTestScore" column="the_test_score"
>> nullValue="-999"/>
>> <result property="schoolScoreAvg" column="schoolScoreAvg"
>> nullValue="-999"/>
>> <result property="distScoreAvg" column="distScoreAvg"
>> nullValue="-999"/>
>> <result property="gradeLevelId" column="grade_level_id"
>> nullValue="-999"/>
>> <result property="schoolName" column="school_name"
>> nullValue="null_string"/>
>> <result property="assessmentId" column="assessment_id"
>> nullValue="-999"/>
>> <result property="schoolRawAvg" column="schoolRawAvg"
>> nullValue="-999"/>
>> <result property="distRawAvg" column="distRawAvg"
>> nullValue="-999"/>
>> <result property="lowcut" column="lowcut" nullValue="-999"/>
>> <result property="highcut" column="highcut"
>> nullValue="-999"/>
>> <result property="prof" column="prof" nullValue="-999"/>
>> <result property="ppId" column="pp_id" nullValue="-999"/>
>> <result property="theRawPoints" column="the_raw_points"
>> nullValue="-999"/>
>> <result property="scoreMethodId" column="score_method_id"
>> nullValue="-999"/>
>> <result property="aboveStandard" column="above_standard"
>> nullValue="-999"/>
>> <result property="schoolAboveStandard"
>> column="school_above_standard" nullValue="-999"/>
>> <result property="districtAboveStandard"
>> column="district_above_standard" nullValue="-999"/>
>> <result property="sessionId" column="session_id"
>> nullValue="-999"/>
>> <result property="maxScore" column="maxScore"
>> nullValue="-999"/>
>> <result property="minScore" column="minScore"
>> nullValue="-999"/>
>> </resultMap>
>>
>> <select id="getStudentProfileDataCombined"
>> resultMap="student_profile_combined_result"
>> parameterClass="java.util.HashMap">
>> $sql$
>> </select>
>>
>> Here is the query:
>>
>> SELECT a.abbrev, tsts.session_name , tsts.session_id, y.display,
>> ts.dateTested, ppl.proficiency_level, ts.test_score AS
>> the_test_score,
>> avs.schoolScoreAvg, avd.distScoreAvg, gr.grade_level_id,
>> sc.school_name,
>> a.assessment_id, avs.schoolRawAvg, avs.school_above_standard,
>> avd.distRawAvg, avd.district_above_standard, pp.lowcut, pp.highcut,
>> pp.prof, pp.pp_id, ts.test_raw_points AS the_raw_points,
>> a.score_method_id,
>> ppl.above_standard, avd.maxScore, avd.minScore
>> FROM assessment a, test_score ts, school sc, grade_level gr,
>> d_avg_test_score avd, s_avg_test_score avs,
>> proficiency_profile_levels ppl,
>> view_pp pp, test_session tsts, test t, school_year y
>> WHERE a.assessment_id = ts.assessment_id and ts.sch_student_id =
>> 13120 AND
>> t.session_id = tsts.session_id AND y.year_id = t.year_id AND
>> a.assessment_id = t.assessment_id AND ts.test_id = t.test_id AND
>> avs.assessment_id = ts.assessment_id AND avs.school_id =
>> ts.school_id AND
>> avs.test_id = ts.test_id AND avs.session_id = t.session_id AND
>> avs.year_id
>> = t.year_id AND avs.grade_level_id = ts.grade_level_id AND
>> avd.assessment_id = ts.assessment_id AND avd.district_id =
>> ts.district_id
>> AND avd.grade_level_id = ts.grade_level_id AND
>> avd.test_id = ts.test_id AND avd.session_id = t.session_id AND
>> avd.year_id
>> = t.year_id AND sc.school_id = ts.school_id AND
>> gr.grade_level_id = ts.grade_level_id AND
>> ppl.proficiency_profile_level_id
>> = pp.ppl_id AND pp.a_id = a.assessment_id AND
>> pp.pp_id = a.primary_prof_profile_id AND ts.grade_level_id =
>> pp.gl_id AND
>> pp.rce_id IS NULL AND t.session_id = pp.s_id AND
>> ts.test_score BETWEEN pp.lowcut AND pp.highcut
>> ORDER BY ts.dateTested DESC
>>
>> TIA,
>> Brian Barnett
>>
>> *********************************************************************
>> *
>> ******
>> This email may contain confidential material.
>> If you were not an intended recipient,
>> Please notify the sender and delete all copies.
>> We may monitor email to and from our network.
>> *********************************************************************
>> *
>> ******
>>
>>
>
> **********************************************************************
> ******
> This email may contain confidential material.
> If you were not an intended recipient,
> Please notify the sender and delete all copies.
> We may monitor email to and from our network.
> **********************************************************************
> ******
>
****************************************************************************
This email may contain confidential material.
If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
****************************************************************************
****************************************************************************
This email may contain confidential
material. If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
***************************************************************************
****************************************************************************
This email may contain confidential
material. If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
***************************************************************************
Re: Query performs differently in SQL Query Analyzer [SOLVED]
Posted by Clinton Begin <cl...@gmail.com>.
Thanks for sharing this Brian.
Keep us posted on anything you learn about this. Even though it's the
driver's problem, we'd be interested if there was anything that we can do
with iBATIS to improve it.
Clinton
On 5/23/05, Barnett, Brian W. <br...@pearson.com> wrote:
>
> I decided to attempt to eliminate the items that Clinton had mentioned
> were outside the iBATIS framework (network, JDBC, driver). I started by
> swapping out the Microsoft JDBC driver with the jTDS JDBC driver for SQL
> Server (http://jtds.sourceforge.net/) and that fixed the performance
> problem with this particular query... woo hooooo!!!
> We have yet to do complete testing throughout the rest of the
> application, but we are hopeful that all other queries will perform at least
> as well as they did with the Microsoft driver.
> Thank you all for your input.
> Brian Barnett
>
> -----Original Message-----
> *From:* Barnett, Brian W.
> *Sent:* Monday, May 23, 2005 2:19 PM
> *To:* 'ibatis-user-java@incubator.apache.org'
> *Subject:* RE: Query performs differently in SQL Query Analyzer
>
> >>where and when do you build your SqlMapClient instance?
> We used JPetStore 4 as a model for using iBATIS. We have a DaoManager
> singleton that gets created during app startup. dao.xml and sql-map-config
> .xml files are parsed and the DaoManager caches instances of our daos.
> >>how often are you connecting?
> Not sure how to answer this.
> >>which datasource are you using?
> Snippet from sql-map-config
> <sqlMapConfig>
> <settings
> cacheModelsEnabled="true"
> enhancementEnabled="true"
> lazyLoadingEnabled="true"
> useStatementNamespaces="false"
> maxSessions="128"
> maxTransactions="32"
> maxRequests="512"/>
> <transactionManager type="JDBC">
> <dataSource type="JNDI">
> <property name="DataSource" value="java:comp/env/bppool"/>
> </dataSource>
> </transactionManager>
> >>are you using lazy loading?
> Yes
> >>join fetching?
> Don't know
> >> complex column mapping via select="" (N+1 selects problem)?
> No
> Here is some additional information:
> 1. We run many, many queries through our web app and none of the others
> exhibit this behavior.
> 2. The performance problem is not seen on smaller databases. (smaller
> number of rows in tables.)
> 3. The following line of code takes 55 seconds to execute
> a. return executeQueryForList("getStudentProfileDataCombined", paramMap);
> b. Where paramMap has one entry called "sql" which contains the SQL
> statement.
> 4. Subsequent calls to the above line of code for the same student come
> back in 1 second.
> 5. We're running 2.0.0 RC5 Build 274.
>
> -----Original Message-----
> *From:* Clinton Begin [mailto:clinton.begin@gmail.com]
> *Sent:* Monday, May 23, 2005 12:30 PM
> *To:* ibatis-user-java@incubator.apache.org
> *Subject:* Re: Query performs differently in SQL Query Analyzer
>
>
>
> >>Each time it encounters a new query?
>
> Let's define new query:
>
> SELECT * FROM STUDENT WHERE STUDENT_ID = #id#
>
> That's one query, regardless of what parameter you use for ?. In iBATIS,
> this is represented as:
>
> But if you have any dynamic parts to the SQL or any $substitutions$, then
> you are potentially creating a new query.
>
> Regardless, there is NOTHING iBATIS does that should take a minute or
> longer, especially after initial runs.
>
> You'll need to provide us with a lot more detail and do some of your own
> profiling to test out your situation properly.
>
> First of all, DO NOT compare iBATIS with queries run from Query
> Analyzer....that's ridiculous. You've eliminated more than iBATIS in that
> scenario, you've also eliminated the network, JDBC, the driver and all of
> your own Java code.
>
> My first questions would be: where and when do you build your SqlMapClient
> instance? how often are you connecting? which datasource are you using? do
> you have any dynamic SQL? are you using maps or beans? are you using lazy
> loading, join fetching, complex colum mapping via select="" (N+1 selects
> problem)?
>
> Performance problems are hard to diagnose properly. Can you give us any
> more information?
>
> Clinton
>
>
> On 5/23/05, Barnett, Brian W. <br...@pearson.com> wrote:
> >
> > When you say iBatis is doing way more than just running SQL, when does
> > it do
> > *way more*? Each time it encounters a new query? Just the first query it
> > processes after startup? Is the *way more* documented somewhere?
> >
> > The SQL I run in Query Analyzer is exactly the same. I pulled it from
> > the
> > logging, which I turned off after your suggestion, but did not see a
> > noticeable difference.
> >
> > -----Original Message-----
> > From: Nathan Maves [mailto: Nathan.Maves@Sun.COM]
> > Sent: Monday, May 23, 2005 11:19 AM
> > To: ibatis-user-java@incubator.apache.org
> > Subject: Re: Query performs differently in SQL Query Analyzer
> >
> >
> > This truly sound like a sql problem. Make sure that the sql that is
> > generated by ibatis is what you run in your analyzer.
> >
> > On a side note make sure that all logging is turned off for your
> > application. This is a huge performance hit if it is turned on for
> > any part of your app.
> >
> > Nathan
> >
> > On May 23, 2005, at 10:53 AM, Barnett, Brian W. wrote:
> >
> > > Yes, I did notice that subsequent times the exact same query performed
> > > better. The problem is that in our app, a teacher is presented with
> > > a list
> > > of students in her class. She wants to run a report on each one.
> > > Clicking on
> > > a student executes the query below but with a different student id.
> > >
> > > It takes over a minute each time she selects a new student. Is this
> > > because
> > > the query is not identical each time? It really does me no good that
> > > subsequent times the query runs better, because the teacher doesn't
> > > need to
> > > run the same student more than once.
> > >
> > > Suggestions?
> > >
> > > -----Original Message-----
> > > From: Nathan Maves [mailto:Nathan.Maves@Sun.COM]
> > > Sent: Monday, May 23, 2005 10:59 AM
> > > To: ibatis-user-java@incubator.apache.org
> > > Subject: Re: Query performs differently in SQL Query Analyzer
> > >
> > >
> > > I think this needs a FAQ :)
> > >
> > > This has been cover quite a few times.
> > >
> > > iBatis is doing way more then just running sql. Make sure that to
> > > truly test the performance of iBatis that you run the query multiple
> > > times. The initial run is creating many object and sets everything
> > > up. Check the speed of the second and third runs to see more accurate
> > > results.
> > >
> > > Nathan
> > >
> > > On May 23, 2005, at 10:13 AM, Barnett, Brian W. wrote:
> > >
> > >
> > >> I have a query that returns in 3 or 4 seconds when I run it in
> > >> Microsoft SQL Query Analyzer but takes over a minute to return when
> > >> run through iBATIS.
> > >> Can anyone give me some clues as to what I should check?
> > >>
> > >> Here is the SQL Map stuff:
> > >>
> > >> <resultMap id="student_profile_combined_result"
> > >> class="java.util.HashMap">
> > >> <result property="abbrev" column="abbrev"
> > >> nullValue="null_string"/>
> > >> <result property="session" column="session_name"
> > >> nullValue="null_string"/>
> > >> <result property="display" column="display"
> > >> nullValue="null_string"/>
> > >> <result property="dateTested" column="dateTested"
> > >> nullValue="null_string"/>
> > >> <result property="proficiencyLevel"
> > >> column="proficiency_level"
> > >> nullValue="null_string"/>
> > >> <result property="theTestScore" column="the_test_score"
> > >> nullValue="-999"/>
> > >> <result property="schoolScoreAvg" column="schoolScoreAvg"
> > >> nullValue="-999"/>
> > >> <result property="distScoreAvg" column="distScoreAvg"
> > >> nullValue="-999"/>
> > >> <result property="gradeLevelId" column="grade_level_id"
> > >> nullValue="-999"/>
> > >> <result property="schoolName" column="school_name"
> > >> nullValue="null_string"/>
> > >> <result property="assessmentId" column="assessment_id"
> > >> nullValue="-999"/>
> > >> <result property="schoolRawAvg" column="schoolRawAvg"
> > >> nullValue="-999"/>
> > >> <result property="distRawAvg" column="distRawAvg"
> > >> nullValue="-999"/>
> > >> <result property="lowcut" column="lowcut" nullValue="-999"/>
> > >> <result property="highcut" column="highcut"
> > >> nullValue="-999"/>
> > >> <result property="prof" column="prof" nullValue="-999"/>
> > >> <result property="ppId" column="pp_id" nullValue="-999"/>
> > >> <result property="theRawPoints" column="the_raw_points"
> > >> nullValue="-999"/>
> > >> <result property="scoreMethodId" column="score_method_id"
> > >> nullValue="-999"/>
> > >> <result property="aboveStandard" column="above_standard"
> > >> nullValue="-999"/>
> > >> <result property="schoolAboveStandard"
> > >> column="school_above_standard" nullValue="-999"/>
> > >> <result property="districtAboveStandard"
> > >> column="district_above_standard" nullValue="-999"/>
> > >> <result property="sessionId" column="session_id"
> > >> nullValue="-999"/>
> > >> <result property="maxScore" column="maxScore"
> > >> nullValue="-999"/>
> > >> <result property="minScore" column="minScore"
> > >> nullValue="-999"/>
> > >> </resultMap>
> > >>
> > >> <select id="getStudentProfileDataCombined"
> > >> resultMap="student_profile_combined_result"
> > >> parameterClass="java.util.HashMap">
> > >> $sql$
> > >> </select>
> > >>
> > >> Here is the query:
> > >>
> > >> SELECT a.abbrev, tsts.session_name , tsts.session_id, y.display,
> > >> ts.dateTested, ppl.proficiency_level, ts.test_score AS
> > >> the_test_score,
> > >> avs.schoolScoreAvg, avd.distScoreAvg, gr.grade_level_id,
> > >> sc.school_name,
> > >> a.assessment_id, avs.schoolRawAvg, avs.school_above_standard,
> > >> avd.distRawAvg, avd.district_above_standard, pp.lowcut, pp.highcut,
> > >> pp.prof, pp.pp_id, ts.test_raw_points AS the_raw_points,
> > >> a.score_method_id,
> > >> ppl.above_standard, avd.maxScore, avd.minScore
> > >> FROM assessment a, test_score ts, school sc, grade_level gr,
> > >> d_avg_test_score avd, s_avg_test_score avs,
> > >> proficiency_profile_levels ppl,
> > >> view_pp pp, test_session tsts, test t, school_year y
> > >> WHERE a.assessment_id = ts.assessment_id and ts.sch_student_id =
> > >> 13120 AND
> > >> t.session_id = tsts.session_id AND y.year_id = t.year_id AND
> > >> a.assessment_id = t.assessment_id AND ts.test_id = t.test_id AND
> > >> avs.assessment_id = ts.assessment_id AND avs.school_id =
> > >> ts.school_id AND
> > >> avs.test_id = ts.test_id AND avs.session_id = t.session_id AND
> > >> avs.year_id
> > >> = t.year_id AND avs.grade_level_id = ts.grade_level_id AND
> > >> avd.assessment_id = ts.assessment_id AND avd.district_id =
> > >> ts.district_id
> > >> AND avd.grade_level_id = ts.grade_level_id AND
> > >> avd.test_id = ts.test_id AND avd.session_id = t.session_id AND
> > >> avd.year_id
> > >> = t.year_id AND sc.school_id = ts.school_id AND
> > >> gr.grade_level_id = ts.grade_level_id AND
> > >> ppl.proficiency_profile_level_id
> > >> = pp.ppl_id AND pp.a_id = a.assessment_id AND
> > >> pp.pp_id = a.primary_prof_profile_id AND ts.grade_level_id =
> > >> pp.gl_id AND
> > >> pp.rce_id IS NULL AND t.session_id = pp.s_id AND
> > >> ts.test_score BETWEEN pp.lowcut AND pp.highcut
> > >> ORDER BY ts.dateTested DESC
> > >>
> > >> TIA,
> > >> Brian Barnett
> > >>
> > >> *********************************************************************
> > >> *
> > >> ******
> > >> This email may contain confidential material.
> > >> If you were not an intended recipient,
> > >> Please notify the sender and delete all copies.
> > >> We may monitor email to and from our network.
> > >> *********************************************************************
> >
> > >> *
> > >> ******
> > >>
> > >>
> > >
> > > **********************************************************************
> > > ******
> > > This email may contain confidential material.
> > > If you were not an intended recipient,
> > > Please notify the sender and delete all copies.
> > > We may monitor email to and from our network.
> > > **********************************************************************
> > > ******
> > >
> >
> > ****************************************************************************
> >
> > This email may contain confidential material.
> > If you were not an intended recipient,
> > Please notify the sender and delete all copies.
> > We may monitor email to and from our network.
> > ****************************************************************************
> >
> >
>
> ****************************************************************************
>
>
> This email may contain confidential
> material. If you were not an intended recipient,
> Please notify the sender and delete all copies.
> We may monitor email to and from our network.
>
> ***************************************************************************
>
>
> ****************************************************************************
>
>
> This email may contain confidential
> material. If you were not an intended recipient,
> Please notify the sender and delete all copies.
> We may monitor email to and from our network.
>
> ***************************************************************************
>
>