p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Hibernate (http://p2p.wrox.com/forumdisplay.php?f=212)
-   -   Sql Server 2000 vs Oracle 9i - performance problem (http://p2p.wrox.com/showthread.php?t=35133)

sniegu November 15th, 2005 06:07 AM

Sql Server 2000 vs Oracle 9i - performance problem
 
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.


shampeter January 27th, 2006 11:31 AM

We feel the same pain here. Ours is a web app using Tomcat 5.0.28, Hibernate 3, SQL Server 2000 and jTDS JDBC driver. We experienced very bad performance but stumbled upon a work-around. We pounded the SQL Server through the web app by JMeter and then run

sp_MSForEachTable 'Update Statistics ? with FullScan'

And all of a sudden, the SQL Server was flying again.

However, this even puzzled me more as I observed the thing as you do. In raw SQL, the performance was great. It was wrong only through the app, i.e. through Hibernate. And the cpu utilization of SQL server went sky high when the performance was wrong. But if it's Hibernate that was wrong, why would SQL server CPU went sky high. It should be the Tomcat's cpu went sky high! Right?

And one final observation was that the SQL server didn't perform whenever it was rebooted. I have to pound it and run update statistics before it would fly again.

Can anyone solve this mystery? [V]


sniegu January 27th, 2006 12:10 PM

Hi shampeter,

I think that we found the cause of SQLServer performance problem ... with a help from Hibernate forum users.
The source of this problem is discussed at address: http://forum.hibernate.org/viewtopic...271698#2271698
I hope that it will help solve SQLServer-Hibernate performance puzzle ;)

Best Regards,
sniegu



All times are GMT -4. The time now is 12:39 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.