I'm doing a query for all revisions of a class that are greater than a timestamp using:
Code:
AuditReaderFactory.get(emf.createEntityManager()).createQuery().forRevisionsOfEntity(clazz, false, true).add(AuditEntity.revisionProperty("timestamp").gt(existingIndex.lastModified())).getResultList();
This is recreating a @ManyToOne referenced object using the query:
Code:
select <audit cols for this type> from <audit table> where DTYPE IN (<class type>) and REV=(SELECT max(REV) FROM <audit table> where TYPE IN (<class type>) and REV <= <maximum revision in revision entity table> and <subquery>.id=<query>.id) and REVTYPE<>2 AND <audit table>.id=<id of entity being restored>
This query is incredibly slow and takes well over 100 minutes just for one entity (in fact, as I'm writing this it's still going). Why is it getting the last revision of the entity (minus DEL revisions)? It's much faster to use a ORDER BY REV LIMIT 1 (or similar for databases that don't have LIMIT). I'm almost wanting to just go straight SQL since this is way too slow. It could also be sped up by using the id straight inside the subquery instead of references the query's table id. I have indexes on DTYPE, REV and REVTYPE, and a unique key on id, REV so it isn't an indexing issue.
I'm not sure why it's using the above query to recreate the referenced object and would appreciate any insight. This is on a MySQL 5.1 database on a Pentium 4 machine, but also takes considerable time on a dual core machine too.
Login
FAQ
Search
