Add StartRevision/EndRevison fileds to audit tables --------------------------------------------------- Key: HHH-3709 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3709 Project: Hibernate Core Issue Type: Improvement Components: envers Affects Versions: 3.4 Reporter: jason shi In Envers audit tables(eg:Person_Aud), two fields added:REV,REVTYPE When retrieve data at special REV, a sql with subselect executed: select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id from Person_AUD a where a.REVTYPE <> ? and a.id = ? and a.REV = (select max(b.REV) from Person_AUD b where b.REV <= ? and a.id = b.id) The sql performance is poor. I suggest adding StartRevision/EndRevison fileds to the audit tables,replace the REV field. The StartRevision equals the original REV field,EndRevision will be filledd when this record changed in next Revision. The new query sql will like this: select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id from Person_AUD a where a.REVTYPE <> ? and a.id = ? and a.StartRevision<=? and a.EndRevision>? -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira