Quick question: is there any difference between hibernate running queries and JDBC from a performance/locking perspective?
Details:
We use Hibernate in many places, and have had good experiences. In this case, we have a complex Oracle View (joins across a DB link), and when we call it from Toad or JDBC it runs in 5-50 seconds. When we run on our web app in Hibernate, it is usually the same amount time. However, occasionally it runs in 5+ minutes or never returns.
We have never reproduced this behavior in TOAD or JDBC, and we know from SQL profiling this is the query it is hanging on. We use session-per-request with detached objects, and do not specify any extra locking params (so it should be the DB default). Also, in this case we arent even using a transaction, and it is read only.
Thoughts?
Thanks,
Chris
ps, I know we have a one-to-many here, but it is not what is causing the performance problem, it is the original query it is hanging on
Hibernate version:
3
Mapping documents:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping
package="edu.upenn.isc.cpr.beans"> <!-- schema="SCHEMA_IF_NOT_DEFAULT" -->
<class name="StudentForList" table="STUDENT">
<id name="id" column="ID" unsaved-value="0">
<generator class="native"/>
</id>
<property name="pennId" column="PENN_ID"/>
<property name="emailAddress" update="false" insert="false" column="EMAIL_ADDRESS"/>
<property name="firstName" update="false" insert="false" column="FIRST_NAME"/>
<property name="middleInitial" update="false" insert="false" column="MIDDLE_NAME"/>
<property name="lastName" update="false" insert="false" column="LAST_NAME"/>
<property name="fullName" update="false" insert="false" column="FULL_NAME"/>
<property name="registeredSection" update="false" insert="false"
column="REGISTERED_SECTION"/>
<property name="registeredSectionDisplay" update="false" insert="false"
column="REGISTERED_SECTION_DISPLAY"/>
<property name="crossListRegisteredSection" update="false" insert="false"
column="CROSS_LIST_REGISTERED_SECTION"/>
<property name="primaryDivision" update="false" insert="false"
column="PRIMARY_DIVISION"/>
<property name="classification" update="false" insert="false" column="CLASSIFICATION"/>
<property name="photoRowId" update="false" insert="false" column="PHOTO_ROW_ID"/>
<property name="athlete" update="false" insert="false" column="IS_ATHLETE"/>
<property name="primaryMajor" update="false" insert="false" column="PRIMARY_MAJOR"/>
<property name="sectionTitle" update="false" insert="false" column="SECTION_TITLE"/>
<property name="sectionSchool" update="false" insert="false" column="SECTION_SCHOOL"/>
<property name="dateCreated" column="DATE_CREATED" />
<property name="userCreated" column="USER_CREATED" />
<property name="dateLastModified" column="DATE_LAST_MODIFIED" />
<property name="modifiedByUser" column="MODIFIED_BY_USER" />
</class>
</hibernate-mapping>
Code:
String STUDENTS_BY_COURSE_AND_TERM = new StringBuffer()
.append(" SELECT ")
.append(" nvl(s.ID, - s.penn_id) as {s.id}, ")
.append(" s.PENN_ID as {s.pennId}, ")
.append(" s.EMAIL_ADDRESS as {s.emailAddress}, ")
.append(" s.FIRST_NAME as {s.firstName}, ")
.append(" s.MIDDLE_NAME as {s.middleInitial}, ")
.append(" s.LAST_NAME as {s.lastName}, ")
.append(" s.FULL_NAME as {s.fullName}, ")
.append(" s.REGISTERED_SECTION_DISPLAY as {s.registeredSectionDisplay}, ")
.append(" s.REGISTERED_SECTION as {s.registeredSection}, ")
.append(" s.CROSS_LIST_REGISTERED_SECTION as {s.crossListRegisteredSection}, ")
.append(" s.PRIMARY_DIVISION as {s.primaryDivision}, ")
.append(" s.CLASSIFICATION as {s.classification}, ")
.append(" s.IS_ATHLETE as {s.athlete}, ")
.append(" s.PRIMARY_MAJOR as {s.primaryMajor}, ")
.append(" s.PHOTO_ROW_ID as {s.photoRowId}, ")
.append(" s.SECTION_TITLE as {s.sectionTitle}, ")
.append(" s.SECTION_SCHOOL as {s.sectionSchool}, ")
.append(" sysdate as {s.dateCreated}, ")
.append(" 0 as {s.userCreated}, ")
.append(" sysdate as {s.dateLastModified}, ")
.append(" 0 as {s.modifiedByUser} ")
.append(" from STUDENT_V {s} ")
.append(" WHERE s.CROSS_LIST_REGISTERED_SECTION = ? ")
.append(" AND s.TERM = ? ").toString();
//snip
hibQuery = this.session.createSQLQuery(query).addEntity((String) aliasOfObject,
(Class) types);
List list = hibQuery.list();
Name and version of the database you are using:oracle 9i
The generated SQL (show_sql=true):Code:
SELECT nvl(s.ID, - s.penn_id) as ID0_, s.PENN_ID as PENN2_2_0_, s.EMAIL_ADDRESS as EMAIL3_2_0_, s.FIRST_NAME as FIRST4_2_0_, s.MIDDLE_NAME as MIDDLE5_2_0_, s.LAST_NAME as LAST6_2_0_, s.FULL_NAME as FULL7_2_0_, s.REGISTERED_SECTION_DISPLAY as REGISTERED9_2_0_, s.REGISTERED_SECTION as REGISTERED8_2_0_, s.CROSS_LIST_REGISTERED_SECTION as CROSS10_2_0_, s.PRIMARY_DIVISION as PRIMARY11_2_0_, s.CLASSIFICATION as CLASSIF12_2_0_, s.IS_ATHLETE as IS14_2_0_, s.PRIMARY_MAJOR as PRIMARY15_2_0_, s.PHOTO_ROW_ID as PHOTO13_2_0_, s.SECTION_TITLE as SECTION16_2_0_, s.SECTION_SCHOOL as SECTION17_2_0_, sysdate as DATE18_2_0_, 0 as USER19_2_0_, sysdate as DATE20_2_0_, 0 as MODIFIED21_2_0_ from STUDENT_V s WHERE s.CROSS_LIST_REGISTERED_SECTION = 'ACCT101001' AND s.TERM = '2005C'
Login
FAQ
Search
