It's too slow to load table's records with Hibernate from Sybase.
For example,a simply one-two-many parent-child relationship,contains 200 records in the parent table and 2000 records in the child table.When loading all of them from Sybase,30 seconds will be wasted but only 3 seconds while loading from Oracle with Hibernate.
This's details below:
Sofeware enviroment:
Hibernate2.0.3
Datebase:Sybase 12.0+Solaris 8/Oracle 9i+WindowsXP
This is the XML mapping files:
There are two tables:Student and Score(one to many).
Code:
Student:
<class name="Student" table="Student">
<id column="id" name="id" type="java.lang.Long" unsaved-value="null">
<generator class="native"/>
</id>
<property column="studentNum" name="studentNum" type="string"/>
<property column="name" name="name" type="string"/>
<property column="gender" name="gender" type="short"/>
<property column="className" name="className" type="string"/>
<set cascade="all-delete-orphan" inverse="true" lazy="false" name="scores">
<key column="studentId"/>
<one-to-many class="Score"/>
</set>
</class>
Score:
<class name="Score" table="Score">
<id column="id" name="id" type="java.lang.Long" unsaved-value="null">
<generator class="native"/>
</id>
<property column="subject" name="subject" type="string"/>
<property column="score" name="score" type="string"/>
<many-to-one cascade="save-update" class="Student" column="studentId" name="owner" not-null="true"/>
</class>
The generated DDL is:
Code:
create table Student (
id NUMERIC(19,0) IDENTITY NOT NULL,
studentNum VARCHAR(255) null,
name VARCHAR(255) null,
gender SMALLINT null,
className VARCHAR(255) null,
primary key (id)
)
create table Score (
id NUMERIC(19,0) IDENTITY NOT NULL,
subject VARCHAR(255) null,
score VARCHAR(255) null,
studentId NUMERIC(19,0) not null,
primary key (id)
)
alter table Score add constraint FK4C04E728A66E8B6 foreign key (studentId) references Student
Configuration of Hibernate:
Code:
hibernate.dialect net.sf.hibernate.dialect.SybaseDialect
hibernate.connection.driver_class com.sybase.jdbc2.jdbc.SybDriver
hibernate.connection.username sa
hibernate.connection.password s8000@gsi
hibernate.connection.url jdbc\:sybase\:Tds\:192.168.82.133\:4100/tempdb
hibernate.jdbc.batch_size 50
hibernate.jdbc.fetch_size 25
hibernate.show_sql false
Test process:
Insert some datas into the tables,subsequencely Student table contains 212 row records and Score table contains 2312 row records.
Use two database: Sybase and Oracle in order to compare.
1.Sybase 12.0 ASE, jconn5.5
2.Oracle 9i, class12.jar
To loading all the data using Hibernate(List l = session.find("from Student as stud")),the
the used time is:
Sybase: 33 seconds
Oracle: 2 seconds
Tracing the Hibernate code,I find the logical loading is:
1.Executes a query to load Student table and pick up each record's id.
2.Execute a query to get all the child records corresponding every record in the Student table.
Code:
select score0_.id as id__, score0_.id as id, score0_.subject as subject, score0_.score as score,
score0_.studentId as studentId from Score score0_ where score0_.studentId=?;
the "?" represent the id of the associated student table record.
3.The time of constructing object could be neglected.
In fact,the Hibernate have executed 213 times query.
What's the problem of the performance with Sybase contrasted with Oracle?
Or is there have any methods to improve the performance of Sybase?
I'm not familiar with Datebase,but I'm expecting for your suggestion!
Thanks!
Login
FAQ
Search
