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 RE: Query performs differently in SQL Query Analyzer
Date Mon, 23 May 2005 17:41:22 GMT
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