-
1. Re: getRevisionNumberForDate SQL
adamw Mar 1, 2013 2:26 PM (in response to whiteside)Wouldn't an index on timestamp work in both cases? Did you maybe do some tests?
Or maybe a compound (timestamp, id) index?
Adam
-
2. Re: getRevisionNumberForDate SQL
whiteside Mar 1, 2013 2:42 PM (in response to adamw)I did try adding a compound index like you say, but it didn't help.
The problem is the '<='. The database has to check every entry that matches that condition to find maximum ID. Since we know the revision IDs are sequential, we can be smarter and just find the maximum timestamp that is less that our number. This means we are only looking at one row, rather than many.
I changed a method in the Envers class org.hibernate.envers.revisioninfo.RevisionInfoQueryCreator
From:
public Criteria getRevisionNumberForDateQuery(Session session, Date date) {
return session.createCriteria(revisionInfoEntityName).setProjection(Projections.max(revisionInfoIdName))
.add(Restrictions.le(revisionInfoTimestampName, timestampAsDate ? date : date.getTime()));
}
To:
public Criteria getRevisionNumberForDateQuery(Session session, Date date) {
return session
.createCriteria(revisionInfoEntityName)
.add(Restrictions.le(revisionInfoTimestampName, timestampAsDate ? date : date.getTime()))
.addOrder(Order.desc(revisionInfoTimestampName))
.setProjection(Projections.property(revisionInfoIdName))
.setMaxResults(1);
}
We have several million rows in our RevisionEntity table. This change reduced our query time from several seconds to several milliseconds.
-
3. Re: getRevisionNumberForDate SQL
adamw Mar 5, 2013 12:21 PM (in response to whiteside)Hmm, but while we assume that a revision "A" is newer than "B" if it has a greater revision number, that doesn't have to be true for timestamps, right? The timestamps may be even identical?
Adam