Hi,
I've got an Events table that stores information about events. The following info about events are stored in that table:
source, severity, dateRecieved and description

After a few days, the size of the table goes over 10,000 records! So i need to purge the database at regular intervals and keep the 1000 most recent records.

In order to do that, I have created a backgound process that runs at regular intervals, and using HQL, I get ALL the records from the table and check the size of the returned collection. If its more than 1000, i delete the older records.

But this is very inefficient and i have also got OutOfMemory Errors. Is there a better HQL query that runs a batch delete and keeps only the 1000 recent records?

Please can somebody help me out with tha query?

Thanks,
Joe.

You can do a batch delete using an sql query like this



If you want to do something else in the session, don't forget do call session.clear() because if you don't do so your session will be desync.
And if your event class is in the second level cache you'll have to call sessionFactory.evict()

My idea would be:
1) to make a select on some sort of timestamp column your event table quite certainly contains - sorting the results.
2) if you get more than 1000 results, find the timestamp of the last entry to keep
3) If you have Hibernate 3, use a bulk delete (see http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#batch-direct), otherwise use directly JDBC to remove all events older than the timestamp found at step 2.

This is certainly a few orders of magnitude more efficient tan what you're doing.

By the way, I would also tend to do the following: wait until the table contains more than say 2000 events, then delete down to 1000 - a matter of taste, not knowing anything else about your application.

Erik