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/23 18:13:10 UTC
Query performs differently in SQL Query Analyzer
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.
****************************************************************************
Re: Query performs differently in SQL Query Analyzer
Posted by Nathan Maves <Na...@Sun.COM>.
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.
> **********************************************************************
> ******
>