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