SQL Server Performance Forum – Threads Archive

Bad JDBC/Hibernate peformance for SQL Server 2000

Hi everyone, 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. We suspect that there could be also a general problem connected with the locking features of the SQL Server database. I’ve heard that in SQL Server 2005 there is a new mechanism for that – is there a chance that our application will be running faster on 2005?
We suspect also that JDBC itself can be the cause – we’ve made a comparison using .NET and JDBC. We have done it with 22 consecutive select statements. It took about 30 ms in .Net and about 200 ms with JDBC. About our database – it contains very few data and the select statements we have problems with are based on ID values so it’s rather not the case of indexes/etc. (we suppose so). 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.

---

Hi ya, One thing to check is whether the JDBC driver is defaulting to use unicode for character strings. If so then a translation needs to be done for every bit of string data This would normally be more of a problem with passing parameters to SQL, and the statements executing slower than expected Cheers
Twan

---

We also tried this option, but it actually doesn’t matter for us – our select statements aren’t based on varchar-parameters but on integer id values.<br />With or without the parameter for encoding the parameter strings as unicode – the performance was as bad as before.<br /><br />PS I noticed that I put my post in the wrong forum. Could anyone please move it to the developer forum?<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /> Thx in advance.

---

So the issue isn’t execution performance of the queries but diagnosing what’s causing the "time gaps"?

---

Actually yes. A single select statament itself runs quickly. But there are these weird pauses between statements when they are sent to the server separately.

---

Hi ya, I’d suggest that you profile the application itself, by writing timestamps to a file between the various function calls. That way you may be able to see whether it is the prepare, binding, execution, fetching rows, etc. causing the problems…? Cheers
Twan

---

i did a test for someone a while back, SQL 2K, MS jdbc drivers.
making about 2000-3000 stored proc calls per sec.
so i don’t think your problem has any thing to do with the driver or difference between 2K and 2K5

---

The tests that you ran… Did you run tests that would generate the same sql more than once? Hibernate makes use of prepared statements in SQL Server. The inital cost of setting up a prepared statement will take extra time. However, subsequent calls to the same query should be faster since the SQL is already stored in the database inside a prepared statement. -Mike

---

I think joechang is right. We switched to 2005 and there is no difference at all <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /> We really don’t know, what we should do now <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /> <br />It’s a fact that our application was previously dedicated fully to Oracle and it’s obvious hard to make it work with SQL Server properly. But for now, I don’t have any ideas left, what could be wrong. Maybe the database structure itself?

---

I’m also trying to debug a similar performance problem with MS SQL Server and JDBC. I’m only having a problem with prepared statements. Code that uses regular Statements execute fine.

---

Perhaps the way that Spring and Hibernate set up connections over JDBC is geared towards Oracle, and not optimal towards SQL Server? For instance with SQL Server you can benefit from connection pooling to minimize the time required for connecting from your client app, but you need to follow a couple of rules to get those benefits. The last remark by johnweidner about prepared statements suggests that it may be wise to check the "buffer cache hit ratio": it should be pretty high, but if it isn’t then SQL Server is probably unable to reuse enough execution plans, for instance because the prepared statements are not in the appropriate format – which I think might be related to the JDBC driver you’re using.

---

What are these rules?

---

Basically: use only a small number of different connection strings. For instance, use integrated security instead of SQL Server logins. SQL Server doesn’t drop an idle connection immediately, and can reuse it for another request if the connection string is identical. Usually means less overhead.

---

I am having similar issues….Hibernate does not seem to even create a Prepared Statement Object from the SQL Server traces I have seen. The problem is I do a million rows insert and before the insert’s do a bunch of select’s. The actual select’s run very qucikly on the server but there is a pause of 2-4 seconds for every SQL being run on the SQL Server hence destroying performance etc. I am no SQL Server 2000 expert but is there any database specific setting related to cursors I need to do in order for the Prepared Statement cache to work.

---

I was having similiar issues on Hibernate / SQL Server 2000 too. But to my surprise, I discovered that the problem was related to MS JDBC DRIVER! Once I switched to use jTDS driver, I saw my prepared SQLs show up on SQL Profiler again! So if you guys are using MS JDBC driver, swap it out and try again. You may be surprised.

---

We have similar issue. Our app work with Oracle and SQL Server via Hibernate. At the moment we see that Oracle is 2-3 times faster. We are using Oracle 9i jdbc driver and Weblogic SQL Server driver. Changing encoding at jdbc level didn’t help. After profiling the app on SQL Server I suspect that SQL Server is slower because of the overhead of creating prepared statements for each jdbc call. I am very interested in another findings if somebody can share them. Vitaliy

---

]]>