Hi everyone,
I would like to direct my question especially to people having some experience with Hibernate and Spring frameworks.
Short problem description:
We have created Spring web application using Hibernate and working with Oracle 9i database (with Spring support for Hibernate mapping).
Everything works great, performance is very high ( database read and write operations times are very short ).
Our problems began when we tried to adapt our application to work with Sql Server 2000 database.
Theoretically, we had to do only a few modifications in our application code (changing some Hibernate mapping files).
From the functionality point of view application works just like with Oracle 9i database but the performance has decreased 10 times !!
We loged execution times of every main operation using Spring Interceptor class.
Sequence of writing and reading 3 groups of objects (3 Hibernate flush and 3 refresh operations ) take 3 seconds for Sql Server 2000 databse while the same operations for Oracle 9i take 300 ms !
Some info about software that we use:
1. Spring ver. 1.2.3
2. Hibernate ver. 3.0.5
3. JDBC drivers for Sql Server:
We tried many drivers (list below) but in every case there was no performance improvement:
- INET Merlia TDS
- jTDS (sourceforge.net) 1.1 i 1.2
- Microsoft driver for MS Sql Server 2000,
- Microsoft driver for MS Sql Server 2005 beta 1, beta 2
4. DataSource and pooling
We use our own DataSource and Pooling class implementations ( we tried also Microsoft solutions but it didn't change anything ).
Based on performed tests we rejected following issues as cause of performance problem:
1. Implementation of Sql Server 2000 - in generall Sql Server is faster then Oracle; we analysed sql statements execution times using Sql Profiler. Execution times were short but there were "time gaps" between each statement execution.
2. JDBC driver - we tested many drivers, many driver parameters combinations but the performance wasn't changing at all.
Our suspicions about cause of Sql Server 2000 performance problems:
1. Hibernate - we tested statements execution times using "pure" JDBC (for the same statements as with Hibernate)
and SQL Server 2000 worked faster than Oracle 9i... so our first suspect was Hibernate
2. Spring with web components - we examined times of execution JUnit tests (tests using Spring mechanisms - AbstractTransactionalSpringContextTests and tests using "pure" Hibernate without Spring).
Execution times for Sql Server 2000 and Oracle 9i were equal. Based on these results we suppose that performance problems can be contected with Spring filters, Spring interceptors creating sessions or transaction servicing mechanisms.
We know that it is hard to figure out problem solution based on our summary information but we hope that somebody encountered already the same problem.
Thanks in advance for any ideas.