I have a Problem with an old (1.1 patched) version of Hibernate and ran into an boring performance Problem on a Live server of a customer.

I have on Use Case where hibernate makes about 5000 select calls to a mssql server, these are executed with sp_prepare and sp_execute

the performance is horrible because the mssql server 2005 doesnt understand that a select in a sp_execute needs not transaction and logs all the select statements in the transaction log (if logging is set to full)

The performance is poor because of the massive data wich gets written to mssql's transaction log (processor and ram is doing nothing noticeable)
but the log file gets blewn up by 1,5gb in a few minutes.

Does someone had the same problem.

Is there a way to tell either mssql server not to log sp_executes when they contain selects?
or tell hibernate not to use prepared statements (probably makes the rest of the system slow?)?

regards sebastian

Dis you find a solution for this problem ?

Hi Sebastian,
we ran into the issue when we had long transactions and the SQL server 2005 transaction log blew to a big number.

From Java Side, if you can make changes then try to have smaller transactions.

From SQL server 2005 taking frequent database backups will clean up (I guess truncate is the term)the transaction log.
Also for the transaction log increase the initial size to a big number for example 1 Gig and enable autogrowth in big numbers of Mega bytes.
and let your application run and see how the transaction log fills up and repeat the process and keep increasing the initial size and the autogrowth until you see the transaction log being stable.

your database administrator might know about this. Or just google on setting the SQL server 2005 transaction log you will find many articles and they explain how the transaction log works and how to optimize and set the log size etc..

The way we resolved our issue is that we changed our product to have smaller transactions and took back ups of the database frequently. Also we did play around with the transaction log size setting but did not move these changes to production because those changes were not necessary.

The reason why SQL Selects are part of the transaction logs is because they are part of the transaction.(I know it does not make any sense to have Selects but i guess that's the reason.)

Please post if you have resolved and how. Just curious.
-Srilatha.