ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Barnett, Brian W." <brian.barn...@pearson.com>
Subject Query performs differently in SQL Query Analyzer
Date Mon, 23 May 2005 16:13:10 GMT
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. 
****************************************************************************

Mime
View raw message