Zolf
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
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?
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?
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?
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
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?
ASKER
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__4ee
2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO TableMetadata : table found: tmp.dbo.AdditionalInsuranc
2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO TableMetadata : columns: [additinalinsurancetypeid,
2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO TableMetadata : foreign keys: [fk_id_vrbls_addtnlinsrncd
2012-02-09 11:05:58,108 [AWT-EventQueue-1] INFO TableMetadata : indexes: [pk__additionalinsura__6d6
2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO TableMetadata : table found: tmp.dbo.AdditionalInsuranc
2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO TableMetadata : columns: [firstnamealternate, melliidno, lastname, additionalinsurancetypeid,
2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO TableMetadata : foreign keys: [fk_id_addtnlinsrnctype_ad
2012-02-09 11:05:59,373 [AWT-EventQueue-1] INFO TableMetadata : indexes: [pk__additionalinsura__6f5