title: How to tweak Hibernate queries
body: <p>I've been left to debug some hibernate code that I did not write. I'll admit straight up that I'm struggling to get my head around how Hibernate puts together queries to this point, so please bear with me.</p>
  
  <p>The problem we're seeing is that the web application is taking an obscene amount of time to load pages (using JSP, Spring and Hibernate).</p>
  
  <p>One of the queries that is being executed references a formula from a mapping that seems to be used twice in the query that hibernate executes. The first reference being a column value, the second it's used as criteria for an inner join.  The second one, in the inner join, is forcing a full table scan which we don't need, not to mention it's making the query really slow.</p>
  
  <p>Is there a way to tweak hibernate queries so that it doesn't use this formula in the query (other than the column value)? Please advise if you require additional information.</p>
  
  <p><strong>Edit:</strong> As per requested, here's the query that I obtained from logging hibernate:</p>
  
  <pre><code>SELECT this_.PARTY_ID       AS PARTY1_23_2_,
    this_.STUDENT_ID          AS STUDENT3_23_2_,
    this_.STUDENT_PIDM        AS STUDENT4_23_2_,
    this_.USERNAME            AS USERNAME23_2_,
    this_.FIRST_NAME          AS FIRST6_23_2_,
    this_.LAST_NAME           AS LAST7_23_2_,
    this_.ACTIVE              AS ACTIVE23_2_,
    subjectenr1_.PARTY_ID     AS PARTY1_24_0_,
    subjectenr1_.OFFERING_ID  AS OFFERING2_24_0_,
    subjectenr1_.RSTS_CODE    AS RSTS3_24_0_,
    subjectenr1_.SITE_CODE    AS SITE4_24_0_,
    subjectenr1_.PROGRAM_CODE AS PROGRAM5_24_0_,
    (SELECT su2.offering_id
    FROM sakaicfg.subject_offering su2
    WHERE su2.offering_id = subjectenr1_.OFFERING_ID
    )                               AS formula0_0_,
    subjectoff2_.OFFERING_ID        AS OFFERING1_25_1_,
    subjectoff2_.CRN                AS CRN25_1_,
    subjectoff2_.IS_ACTIVE          AS IS3_25_1_,
    subjectoff2_.TEACHING_SCHOOL    AS TEACHING4_25_1_,
    subjectoff2_.CAMPUS             AS CAMPUS25_1_,
    subjectoff2_.START_SEMESTER     AS START6_25_1_,
    subjectoff2_.STUDYMODE          AS STUDYMODE25_1_,
    subjectoff2_.SUBJECT_ID         AS SUBJECT8_25_1_,
    subjectoff2_.GRADE_MARKING_CODE AS GRADE9_25_1_,
    (SELECT MAX(su2.effective_semester)
    FROM sakaicfg.subject su2
    WHERE su2.subject_id        = subjectoff2_.SUBJECT_ID
    AND su2.effective_semester &lt;= subjectoff2_.START_SEMESTER
    ) AS formula1_1_
  FROM SAKAICFG.STUDENT this_
  INNER JOIN SAKAICFG.SUBJECT_ENROLMENT subjectenr1_
  ON this_.PARTY_ID=subjectenr1_.PARTY_ID
  INNER JOIN SAKAICFG.SUBJECT_OFFERING subjectoff2_
  ON (SELECT su2.offering_id
    FROM sakaicfg.subject_offering su2
    WHERE su2.offering_id     = subjectenr1_.OFFERING_ID)=subjectoff2_.OFFERING_ID
  WHERE this_.ACTIVE          ='Y'
  AND subjectenr1_.RSTS_CODE &lt;&gt;'DD'
  AND subjectoff2_.IS_ACTIVE  ='Y'
  AND subjectoff2_.OFFERING_ID='35505'
  ORDER BY this_.PARTY_ID DESC;
  </code></pre>
  
  <p>The part in question is this excerpt:</p>
  
  <pre><code>  (SELECT su2.offering_id
    FROM sakaicfg.subject_offering su2
    WHERE su2.offering_id = subjectenr1_.OFFERING_ID
    )
  </code></pre>
  
  <p>The mapping xml file for the Subject Enrollment table contains the following relationships:</p>
  
  <pre><code>&lt;many-to-one name="student" entity-name="CsuActiveStudentDto" column="PARTY_ID" insert="false" update="false" access="field" unique="false"/&gt;
  &lt;many-to-one name="subjectOffering" entity-name="CsuSubjectOfferingDto" insert="false" update="false" access="field" unique="false"&gt;
     &lt;formula&gt;(select su2.offering_id
              from sakaicfg.subject_offering su2 where su2.offering_id = OFFERING_ID)&lt;/formula&gt;
  &lt;/many-to-one&gt;
  &lt;one-to-one name="student" class="au.edu.csu.enterprise.domain.StudentDto" property-ref="enrolment"/&gt;
  &lt;many-to-one name="offering" class="au.edu.csu.enterprise.domain.SubjectOfferingDto" column="OFFERING_ID"/&gt;
  </code></pre>
  
  <p>I have been working with one of our DBA's looking at the queries and isolating ones that need looking at, that's how we found this one.</p>