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.

i tell you why - sql server (sybase engine) block reader page and don't do rollbacking
single select is quicker , but reader block another readers or writers
for oracle, reader don't block another readers or writers and writer don't block another readers or writers (writer block another writer for write to same row, only) - it is true MVC

you can see true oracle performance advantages when you use application with many users and with many statements - it is your case with spring/hibernate probably - try test with only one statement in spring/hibernate

for your tests - if you test performance for statements one to one in jdbc you haven't blocking and sql server work quicker - when you execute statmemnets in undetermined order then sql server do blocking and you have worst performance

testing one statement in one time isn't preformance test for database - you have to test more statements and transactions
if you use flat file you get quicker performave for contents list than select from any databases, too

what is your reason for transfer to sql server ?

Hi snpesnpe,
thanks for your help :)



That's exactly the cause of our Sql Server performance problems.

We have made a simple tests:
At first we have measured execution time of sequence containing 23 separate statements (simple "select" statements) - it took about 500ms.
In second test we "joined" all these statements code into single string and executed single statement - it took about 30ms :)



We work with Oracle 9i database but our application will be delivered to other users and some of them use Sql Server.

Is there any "switch" - "property" in Hibernate framework which allows to turn on some kind of "statements string" caching while performing Refresh or Flush operations?
i.e. is it possible in Hibernate to "build" statement string containing "refresh" statements for all "child" objects during Refresh operation of given object and execute single statement at the end of Refresh operation instead of executing separate Refresh statements for all nested objects ?

Thanks,
sniegu

you can do nothing smart for sql server - if you do what you tell then you will help with one user, but for more users it don't help

upgrade to oracle form sql server is free - if you have sql server licence and you reject it you get oracle license without fee

Hi snpesnpe,
thanks for info - it would be easier to switch to Oracle 9i but it dosn't depend on us.
People which we deliver our systems to have many other applications working on Sql Server and it would be very hard to convince them to switch to Oracle database (costs of such operation would be huge).

So now we know that performance problem is not connected to Hibernate and we have to find solution in Sql Server and JDBC driver mechanisms optimisation.

Best Regards,
sniegu

There is possibility to control Sql Server locking mechanism (if I understand that we are talking about same issue here... ) in SQL;

http://www.sql-server-performance.com/r ... _hints.asp

We are using (non hibernate) Sql to w/lock hint for example: 'select xyz from abc with nolock'. This is ,for us, mainly to prevent dreaded Sql 'exec sp_who2' 'BlkBy' race deadlocks.

Ability to set Sql Server lock hints w/HQL would be nice feature :)

and what ? you have dirty read and who know what ever - if it good why MS don't set it default ? - I think because it broke base relational database requests

for example, if you set nolock and user A add row while user B do query - what will happen ?
sql server is good for one user or one select type application

Our software can live w/dirty data where we use nolock hint. Also there is other locking options than nolock.

i agree that any software can live.
if you haven't many users/read/transactions you can live - you can live with this system too, but performance will be much worst
I don't want think about performance when system increase (data increase, complexity, users,transactions) and i use oracle.
with sql server you will have once situation similar sniegu - why performance decrease for xxx times
this is true for all non-MVC databases

last versions postgresql are great, too, but i have problem with object depndency in big system (when i change any functions/procedure i have to remove all depend objects) - postgresql > 8.0 is MVC too

adding :
dirty data don't make problem in every situations - your system can work fine for years, but once it can have incosistent state

problem without lock is performnace problem and it isn't fatal, but lock/nolcok problem can be fatal

I agree that Oracle 9i-> usually gives much better performance under concurrent heavy load than Sql server 2k, exp if configured by professional. Sql Server 2005 in other case would be interesting to test performwise as Sybase core has finally been kicked out of it (complete rewrite) if I recall right.

performance test in concurent environment is hard, but one thing is important - is sql server 2005 MVC (Multiversion Concurrency Control) database ?

short description mvc (postgresql) http://www.postgresql.org/docs/8.1/static/mvcc.html

In Sql Server 2005 MVCC it is called 'snapshot' isolation afaik. Ofcourse it is first version of MS implementation feature, which have been in Oracle many years now. I think we get 'hands-on' testresults/comparisons etcetc quite soon now when official release of 2005 is out.

is is called 'snapshot isolation' because it isn't MVCC - sql server 'isolate' only last commited row
and you can do 'unclocked read', but it isn't true MVCC
with MVCC 'reader don't block reader and writer' and 'writer don't block reader or writer' (except same row writing, of course) - sql server tell nothing about wirting locking in new features
it is cosmetic change only (from concurency and transaction point)

performance will be better for more read operations, worst for one read

you will see performance decreasing when many users do many transactions

I'm impress with Microsoft - they have poorly, high-priced database and very good market position