CODEWITHSUNDEEP
javadatabasehibernatespring
Cyntech
Cyntech

Reputation: 5572

How to tweak Hibernate queries

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.

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).

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.

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.

Edit: As per requested, here's the query that I obtained from logging hibernate:

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 <= 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 <>'DD'
AND subjectoff2_.IS_ACTIVE  ='Y'
AND subjectoff2_.OFFERING_ID='35505'
ORDER BY this_.PARTY_ID DESC;

The part in question is this excerpt:

  (SELECT su2.offering_id
  FROM sakaicfg.subject_offering su2
  WHERE su2.offering_id = subjectenr1_.OFFERING_ID
  )

The mapping xml file for the Subject Enrollment table contains the following relationships:

<many-to-one name="student" entity-name="CsuActiveStudentDto" column="PARTY_ID" insert="false" update="false" access="field" unique="false"/>
<many-to-one name="subjectOffering" entity-name="CsuSubjectOfferingDto" insert="false" update="false" access="field" unique="false">
   <formula>(select su2.offering_id
            from sakaicfg.subject_offering su2 where su2.offering_id = OFFERING_ID)</formula>
</many-to-one>
<one-to-one name="student" class="au.edu.csu.enterprise.domain.StudentDto" property-ref="enrolment"/>
<many-to-one name="offering" class="au.edu.csu.enterprise.domain.SubjectOfferingDto" column="OFFERING_ID"/>

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.

Upvotes: 1

Views: 1626

Answers (1)

Ralph
Ralph

Reputation: 120761

You can override the Hibernate Create Read Update and Delete statements by custom SQL (not hql) for each entity (and collections too).

For more details have a look at the Hibernate Reference: Chapter 17.3. Custom SQL for create, update and delete and for how it works in the Hibernate Annotation Reference: Chapter 2.4.10. Custom SQL for CRUD operations

@Entity
@SQLInsert( sql="INSERT INTO Demo(name, id) VALUES(?,?)")
@Loader(namedQuery = "betterLoad")
@NamedNativeQuery(name="betterLoad",
        query="select id, name from Demo where id= ?",
        resultClass = Demo.class)
public class Demo {
    @Id
    private Long id;
    private String name;
}

Upvotes: 0

Related Questions