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:[EMAIL PROTECTED] 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:[EMAIL PROTECTED] > 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. ****************************************************************************
