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.
****************************************************************************
