Link to home
Start Free TrialLog in
Avatar of Zolf
ZolfFlag for United Arab Emirates

asked on

Hibernate and mssql server 2005 db

Hello there,

Our application uses hibernate3 with MSSQL 2005 server platform. Hibernate configured to update DB structure on startup (hibernate.hbm2ddl.auto property to update). Since last week a problem raised that application starts very slow. Also if to check DB there are no all the necessary indexes which should be created by hibernate. If to set hibernate.hbm2ddl.auto to validate application starts fast but it is not a solution such as we often change data structure (application is in development). DBCC CHECKDB for the database gives no warnings. Also hibernate doesn't report any errors. If to restore previous database version of one week ago the problem disappears. But we have a lot of new data since that date entered and don't want to loose it. How to find the cause of so strange behaviour? What can be corrupted in the DB that doesn't allow to create all the indexes.
ASKER CERTIFIED SOLUTION
Avatar of girionis
girionis
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Zolf

ASKER

the slowest performance is on tablemetadata validation. As you see at least one second per table.


2012-02-09 11:05:56,858 [AWT-EventQueue-1] INFO  TableMetadata : table found: tmp.dbo.AdditionalDiscount

2012-02-09 11:05:56,858 [AWT-EventQueue-1] INFO  TableMetadata : columns: [amount, id, receiptid, updatedby, created, relatedexpenseid, lastupdated, createdby]

2012-02-09 11:05:56,858 [AWT-EventQueue-1] INFO  TableMetadata : foreign keys: [fk_id_tafsil_rcadddisc, fk_id_rcpt_rcadddisc]

2012-02-09 11:05:56,858 [AWT-EventQueue-1] INFO  TableMetadata : indexes: [pk__additionaldiscou__4ee89e87]

2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO  TableMetadata : table found: tmp.dbo.AdditionalInsuranceDivisionInformation

2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO  TableMetadata : columns: [additinalinsurancetypeid, employeeportionofadditionalinsurance, id, updatedby, created, variablesid, employerportionofadditionalinsurance, lastupdated, createdby]

2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO  TableMetadata : foreign keys: [fk_id_vrbls_addtnlinsrncdvsn, fk_id_addtnlinsrnctype_addtnlinsrncdvsn]

2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO  TableMetadata : indexes: [pk__additionalinsura__6d6df0fe]

2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO  TableMetadata : table found: tmp.dbo.AdditionalInsurancePersonInformation

2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO  TableMetadata : columns: [firstnamealternate, melliidno, lastname, additionalinsurancetypeid, lastupdated, dateofbirth, createdby, firstname, additionalinsurancestartdate, id, created, updatedby, relationshipid, additionalinsuranceenddate, gender, lastnamealternate, personalidno, included, personalinformationid]

2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO  TableMetadata : foreign keys: [fk_id_addtnlinsrnctype_addtnlinsrncprsninf, fk_id_rltnshp_addtnlinsrncprsninf, fk_id_prsnlinf_addtnlinsrncprsninf]

2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO  TableMetadata : indexes: [pk__additionalinsura__6f563970]
This is normal behaviour since you are validating the tables, it takes time. I really would not worry about it since this is the development process. On production you don't have these issues since you disable validation and excessive logging. We were using table validation here as well, and when we disabled it (when we went into production) we noticed a x5 increase in startup and processing speed.
Avatar of Zolf

ASKER

This is normal behaviour since you are validating the tables, it takes time. I really would not worry about it since this is the development process. On production you don't have these issues since you disable validation and excessive logging. We were using table validation here as well, and when we disabled it (when we went into production) we noticed a x5 increase in startup and processing speed.

But that validation was working fast on the previous DB version which is one week old with the same tables set.
Are you sure the validation was working before? Can you compare both the persistence.xml files (the current one and the one that is one week old) and see if theer are any differences? Something else you need to bare in mind is that if someone else is using the db at the same time. This is something we were experiencing as well at times and we noticed that at some specific intervals backup was running and the db was getting really slow.

Also notice that adding excessive logging in your application slows the performance significantly. Did you hve logging enabled a week before? What happens if you disable hibernate logging?
Avatar of Zolf

ASKER

I am running the same application version with different DBs. One is one week old and current one which is how i think corrupted somewhere. Db is not used anywhere. Logging was added only now to find the slowest part during startup operation. If to use db which is one week old table meta data validation for all tables takes only few seconds. But with the latest db it takes 1-2 seconds per each table
> I am running the same application version with different DBs.

When you say different DBs do you mean different vendors (like Oracle and Microsoft) or same db but two distinct installations, one in computer A the second on computer B?
Avatar of Zolf

ASKER

>>When you say different DBs do you mean different vendors

no same db ie. mssql server 2005.


> I am running the same application version with different DBs.

here i mean db backup one on 2/2/2012 and another yesterdays backup
You said that you have changed the tables structure, so I still think this is normal behaviour. HAve you tried using a database monitoring tool to see if there are any problems?