ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brandon Goodin <brandon.goo...@gmail.com>
Subject Re: Query performs differently in SQL Query Analyzer
Date Mon, 23 May 2005 18:11:53 GMT
IBatis caches the column mappings on results. This is because it needs
to determine how to map the jdbc column type with the corresponding
java type. Once it has figured out what and how it needs to be
translated it caches it. So, the second time you run the query it will
be faster. But, make sure your JVM is shutting down and then starting
again (as in a unit test may do). Also, you can try specifying your
jdbcType and javaType in the result tags.

Brandon

On 5/23/05, Barnett, Brian W. <brian.barnett@pearson.com> wrote:
> 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:Nathan.Maves@Sun.COM]
> 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:Nathan.Maves@Sun.COM]
> > 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.
> ****************************************************************************
>

Mime
View raw message