Hibernate version: 3.1.3

Name and version of the database: MySQL 5.0.24a

Hi everyone,

I want to optimise the performance of complex delete operations (across several tables) by executing custom SQL code using a Hibernate session. I am using first and second level caches. How would a custom delete operation affect these caches since I am "bypassing" HQL and SQL code generated by hibernate.

Will hibernate automatically update its caches in such a way that any data that has been deleted by custom SQL code in the database is synchronized with the hibernate caches, or will hibernate keep data in its caches that has been deleted? What happens if another user tries to update data from a cache (eg. second level cache) which has already been deleted by custom SQL?

What are the best practices regarding custom SQL for complex delete operations?

I am currently doing this and would be interested in additional feedback. The system I am building is actually a hybrid using a proprietary solution with hibernate and so, i am often inserting/updating through the proprietary system and getting data via hibernate. From my initial tests, I found that anytime i work with the proprietary solution to insert/update/delete, I need to call refresh on the session (e.g. hibernateSession.refresh()), otherwise if I immediately retrieved the data after doing an update or delete, I got stale data.

Unfortunately, i have no idea if this is the best practice or not... I would be interested in other's feedback.

HTH,

Paul

If my response has been helpful, please rate...